﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using fuzhu;
using System.Data.SqlClient;

namespace GLY_XieTong
{

    public class XieTong
    {


        // A,B 账连接字符串
        public string cConStrA { get; set; }
        public string cConStrB { get; set; }

        //中间库
        public string cConStr { get; set; }

        //public string cSourceCodeColA;
        //public string cSourceCodeColB;
        //public string cTargetCodeColA;
        //public string cTargetCodeColB;

        //单据插入的操作类型
        string cOperation = "cOperation";

        //public string cError { get; set; }
        public string  cDBID { get; set; }

        public XieTong(string conStr, string DBID)
        {
            cDBID = DBID;
            cConStr = conStr;
            //GetDBConStr(conStr, DBID);

            //cSourceCodeColA = GetSourceCodeColA();
            // cSourceCodeColB = GetSourceCodeColB();
            // cTargetCodeColA = GetTargetCodeColA();
            // cTargetCodeColB = GetTargetCodeColB();
        }
        /// <summary>
        /// 获得源数据和目标数据的字符串连接方式
        /// </summary>
        /// <param name="conStr"></param>
        /// <param name="DBID"></param>
        public string GetDBConStr(string conStr, string DBID)
        {
            string cError = "";
            string sql = string.Format("select CSOURCE,CTARGET from zdy_inter_database WHERE DBID = '{0}' ", DBID);
            DataTable dataTable = DbHelper2.ExecuteTable(sql, conStr);
            if (dataTable.Rows.Count > 0)
            {
                cConStrA = DbHelper.GetDbString(dataTable.Rows[0]["CSOURCE"]);
                cConStrB = DbHelper.GetDbString(dataTable.Rows[0]["CTARGET"]);
                cError = "";
            }
            else
            {
                cConStrA = "";
                cConStrB = "";
                cError = "同步账套出错！";

            }
            return cError;
        }


        #region 同步U8单据
        public string InsAll(string cLx, string cSourceCode)
        {

           string  cError = "";

            try
            {
                //选好读取dbid，然后进行数据传输
                string sqldb = " SELECT dbid FROM dbo.zdy_inter_database  ";
                DataTable dtdb = DbHelper2.ExecuteTable(sqldb, cConStr);
                for (int m = 0; m < dtdb.Rows.Count; m++)
                {
                    cDBID = DbHelper.GetDbString(dtdb.Rows[m]["dbid"]);
                    string cRe = GetDBConStr(cConStr, cDBID);
                    if (cRe != "")
                    {
                        cError += cDBID + "连接字符串未配置！\r\n";
                        continue;
                    }

                    if (cLx == "all")
                    {

                        string sql = string.Format(@"SELECT A.DBID,a.CLX,c.id
  FROM [ZDY_INTER_XIETONG] a
  INNER JOIN dbo.zdy_inter_database b ON a.DBID=b.DBID
  INNER JOIN zdy_inter_table  c ON a.CLX=c.clx
  WHERE a.DBID='{0}' and  b.BUSE = 1 ", cDBID);
                        DataTable dt = DbHelper2.ExecuteTable(sql, cConStr);
                        if (dt.Rows.Count > 0)
                        {
                            //循环判断档案进行同步
                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                string cLxmx = DbHelper.GetDbString(dt.Rows[i]["CLX"]);
                                int iId = DbHelper.GetDbInt(dt.Rows[i]["id"]);

                                cError += CheckVouch(cLxmx, iId, cSourceCode);

                            }
                        }
                        //else
                        //{
                        //    cError += cDBID + "数据库同步号错误\r\n";
                        //}
                    }
                    else if (cLx == "alltoU8")
                    {
                        if (cDBID.Contains("-2"))
                        {
                            string sql = string.Format(@"SELECT A.DBID,a.CLX,c.id
  FROM [ZDY_INTER_XIETONG] a
  INNER JOIN dbo.zdy_inter_database b ON a.DBID=b.DBID
  INNER JOIN zdy_inter_table  c ON a.CLX=c.clx
  WHERE a.DBID='{0}' and  b.BUSE = 1 ", cDBID);
                            DataTable dt = DbHelper2.ExecuteTable(sql, cConStr);
                            if (dt.Rows.Count > 0)
                            {
                                //循环判断档案进行同步
                                for (int i = 0; i < dt.Rows.Count; i++)
                                {
                                    string cLxmx = DbHelper.GetDbString(dt.Rows[i]["CLX"]);
                                    int iId = DbHelper.GetDbInt(dt.Rows[i]["id"]);

                                    cError += CheckVouch(cLxmx, iId, cSourceCode);

                                }
                            }
                        }
                        //else
                        //{
                        //    cError += cDBID + "数据库同步号错误\r\n";
                        //}
                    }
                    else if (cLx == "alltoZJK")
                    {
                        if (!cDBID.Contains("-2"))
                        {
                            string sql = string.Format(@"SELECT A.DBID,a.CLX,c.id
  FROM [ZDY_INTER_XIETONG] a
  INNER JOIN dbo.zdy_inter_database b ON a.DBID=b.DBID
  INNER JOIN zdy_inter_table  c ON a.CLX=c.clx
  WHERE a.DBID='{0}' and  b.BUSE = 1 ", cDBID);
                            DataTable dt = DbHelper2.ExecuteTable(sql, cConStr);
                            if (dt.Rows.Count > 0)
                            {
                                //循环判断档案进行同步
                                for (int i = 0; i < dt.Rows.Count; i++)
                                {
                                    string cLxmx = DbHelper.GetDbString(dt.Rows[i]["CLX"]);
                                    int iId = DbHelper.GetDbInt(dt.Rows[i]["id"]);

                                    cError += CheckVouch(cLxmx, iId, cSourceCode);

                                }
                            }
                        }
                        //else
                        //{
                        //    cError += cDBID + "数据库同步号错误\r\n";
                        //}
                    }
                    else
                    {
                        //具体单据的同步
                        string sql = string.Format(@"SELECT A.DBID,a.CLX,c.id
  FROM [ZDY_INTER_XIETONG] a
  INNER JOIN dbo.zdy_inter_database b ON a.DBID=b.DBID
  INNER JOIN zdy_inter_table  c ON a.CLX=c.clx
  WHERE a.DBID='{0}' and  b.BUSE = 1  AND a.clx='{1}' ", cDBID, cLx);
                        DataTable dt = DbHelper2.ExecuteTable(sql, cConStr);
                        if (dt.Rows.Count > 0)
                        {

                            int iId = DbHelper.GetDbInt(dt.Rows[0]["id"]);

                            cError += cDBID + ":"+CheckVouch(cLx, iId, cSourceCode)+"\r\n";


                        }
                        //else
                        //{
                        //    cError += cDBID +":数据库同步号错误\r\n";
                        //}
                    }
                }
                return cError;

            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
        #region 判断使用哪个同步
        private string  CheckVouch(string cLxmx, int iId, string cSourceCode)
        {
            string cError = "";
            int idout = 0;
            //档案开头是XTA，是基础档案协同
           if (cLxmx.StartsWith("XTA"))
            {

                cError += cLxmx + "同步结果:" + InsU8Voucher(cLxmx, cSourceCode,out idout) + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\r\n";
                ////插入副表
                //InsFuBiao(cLxmx);
                
                //if (cLxmx != "XTA存货档案sub")
                //{
                //    cError += DelRecordNew(cLxmx, "N");
                //}
            }
            else if (cLxmx.StartsWith("XTB"))
            {
                //U8插入到其他表
                cError += cLxmx + "同步结果:" + INSXieTongDangAn(cLxmx) + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\r\n";
            }
            else if (cLxmx.StartsWith("XTC"))
            {
                cError += cLxmx + "同步结果:" + INSU8VouchToOther2(cLxmx, cSourceCode, cDBID) + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\r\n";
            }
            return  cError;
        }
        #endregion
        #endregion



        #region  单据插入目标单据，按行进行插入
        /// <summary>
        /// /// <summary>
        /// U8单据，生成目标单据
        /// </summary>    
        /// </summary>
        /// <param name="cLx">接口类型</param>
        /// <param name="cfilter">过滤条件</param>
        /// <param name="idout">生成单据的id</param>
        /// <param name="ccode2">生成单据的ccode</param>
        /// <returns></returns>

        public string VoucherToVoucher(string cLx, string cSourcePK)
        {
            string cError = "";
            try
            {
                //1 是主表  2 是子表
                string cRe = "";
                int iChild = 0;   //插入表的子表id
                int iId = 0;     //插入表的主表id             
                string sql = string.Format(" SELECT clx,cfrom,ctable,ctables,cidentity,czjbtable,PK,PKS,ctimestamp,cardnumber,csysqianzhui,czjbfilter,czjbpk,czjbcode,czjbtables,bgrade FROM dbo.zdy_inter_table WHERE CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cFrom = DbHelper.GetDbString(dt1.Rows[0]["cfrom"]);
                    //用于定位主键id
                    string cZjbFilter = DbHelper.GetDbString(dt1.Rows[0]["czjbfilter"]);
                    string cTarTable1 = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cTarTable2 = DbHelper.GetDbString(dt1.Rows[0]["ctables"]);
                    string cIdentity = DbHelper.GetDbString(dt1.Rows[0]["cidentity"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string cPKS = DbHelper.GetDbString(dt1.Rows[0]["PKS"]);
                    string cZjbPK = DbHelper.GetDbString(dt1.Rows[0]["czjbpk"]);   //中间表的主键 ，即编码
                    string cZjbCode = DbHelper.GetDbString(dt1.Rows[0]["czjbcode"]);   //中间表的单据编码，即编码
                    string cZjbTable = DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);  //中间表主表的数据
                    string cCardnumber = DbHelper.GetDbString(dt1.Rows[0]["cardnumber"]);
                    string cSysQianZhui = DbHelper.GetDbString(dt1.Rows[0]["csysqianzhui"]);
                    string ccode2 = "";
                    //用在主表查询上
                    if (!string.IsNullOrEmpty(cSourcePK) && cSourcePK != "")
                    {
                        cZjbFilter += string.Format(" and {0}='{1}' ", cZjbPK, cSourcePK);
                    }
                    //主表字段
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cvalue,'')<>'' and  ctable = '{1}' FOR XML PATH('')),1,1,'')", cLx, cTarTable1);
                    string cColumn1 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //子表字段
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cvalue,'')<>'' and  ctable = '{1}' FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cColumn2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //主表值
                    sql = string.Format(@"  SELECT STUFF((select ','+cvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cvalue,'')<>'' and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", cLx, cTarTable1);
                    string cValue1 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //子表值
                    sql = string.Format(@"  SELECT STUFF((select ','+cvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cvalue,'')<>'' and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cValue2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));


                    //更新的主表字段，单据不支持更新
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn+'=Tmp.'+replace(replace(cvalue,'a.',''),'b.','') from zdy_inter_tables WHERE clx = '{0}'
                    AND ISNULL(cvalue,'')<>''  and bupdate=1 and  ctable = '{1}' FOR XML PATH('')),1,1,'')", cLx, cTarTable1);
                    string cUpdate1 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //更新的子表字段
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn+'=Tmp.'+replace(replace(cvalue,'a.',''),'b.','') from zdy_inter_tables WHERE clx = '{0}'
                    AND ISNULL(cvalue,'')<>'' and bupdate=1 and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cUpdate2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //                    //主表和关联，子表的关联,取主键对应的字段. 删除时也会用到
                    //子表通过关联主表进行更新
                    string sqlcon = string.Format("SELECT  ccolumn FROM dbo.zdy_inter_tables WHERE clx='{0}' and ctable = '{1}' and  cvalue='{2}' ", cLx, cTarTable1, cZjbCode);
                    string cCodeColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sqlcon, cConStr));
                    string cUpdateWhere = string.Format(" Tmp.{0} = b.{1}", cZjbCode, cCodeColumn);

                    string cCodeColumn2 = "";
                    if (!string.IsNullOrEmpty(cTarTable2) && cTarTable2 != "")
                    {
                        sqlcon = string.Format("SELECT  ccolumn FROM dbo.zdy_inter_tables WHERE clx='{0}' and ctable = '{1}' and  cvalue='{2}' ", cLx, cTarTable2, cZjbCode);
                        cCodeColumn2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sqlcon, cConStr));

                    }
                    //原始数据的插入栏目
                    //主表字段
                    sql = string.Format(@"  SELECT STUFF((select distinct ','+csourcecol from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(csourcecol,'')<>''   FOR XML PATH('')),1,1,'')", cLx, cTarTable1);
                    string cSourcCol = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //创建目标库临时表，目标库中有源表
                    var tempTableIns = @"#TempIns" + cTarTable1;
                    //var createtempsqlIns = string.Format("select {0} into {1} {2} where 1=2", cSourcCol, tempTableIns, cFrom);
                    sql = string.Format("select {0} {1} where 1=2", cSourcCol, cFrom);
                    DataTable dtIns = DbHelper2.Execute(sql, cConStrA).Tables[0];
                    var createtempsqlIns = CreateTable(dtIns.Columns, tempTableIns);

                    //取参数,单据号 
                    string cIDname = "", cIDname2 = "", cCodeName = "", cSysCodeName = "", cbSysCodeName = "", cCodeName2 = "";
                    string cAutoIDname = "", cRowNoName = "";


                    //id
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cvalue = '@id'", cLx, cTarTable1);
                    cIDname = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cvalue = '@ccode'", cLx, cTarTable1);
                    cCodeName = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //cSysCodeName
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cvalue = '@csysbarcode'", cLx, cTarTable1);
                    cSysCodeName = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //id2
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cvalue = '@id'", cLx, cTarTable2);
                    cIDname2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //ccode,先临时赋值，生成成功后再update
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cvalue = '@ccode'", cLx, cTarTable2);
                    cCodeName2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //autoid
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cvalue = '@autoid'", cLx, cTarTable2);
                    cAutoIDname = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //rowno
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cvalue = '@irowno'", cLx, cTarTable2);
                    cRowNoName = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //cbSysCodeName
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cvalue = '@cbsysbarcode'", cLx, cTarTable2);
                    cbSysCodeName = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));


                    //从中间表主表取数进行遍历
                    //int idoutNew = 0;
                    //string ccodeNew = "";
                    string sqlFilter = string.Format("select {0},{1},{2}  from {3} where {4}", cZjbPK, cOperation, cZjbCode,
                     cZjbTable, cZjbFilter);
                    DataTable dt = DbHelper2.ExecuteTable(sqlFilter, cConStrA);
                    if (dt.Rows.Count == 0)
                    {
                        if (string.IsNullOrEmpty(cSourcePK))
                        {
                            throw new Exception(string.Format("单据已全部协同！"));
                        }
                        else
                        {
                            throw new Exception(string.Format("单据已全部协同或编码未同步！"));
                        }

                    }
                    else
                    {
                        //循环，处理插入数据
                        for (int k = 0; k < dt.Rows.Count; k++)
                        {
                            string cZjbPKValue = DbHelper.GetDbString(dt.Rows[k][cZjbPK]);
                            string cZjbCodeValue = DbHelper.GetDbString(dt.Rows[k][cZjbCode]);
                            string cOperationValue = DbHelper.GetDbString(dt.Rows[k][cOperation]);
                            if (string.IsNullOrEmpty(cOperationValue) || cOperationValue == "")
                            {
                                cOperationValue = "0";
                            }
                            if (cOperationValue == "1")
                            {
                                cRe += cZjbPKValue + "不支持更新操作，请先删除再录入；";
                                continue;
                            }
                            //检查目标库是否存在
                            string cCheckRe = CheckGen(cLx, cZjbCodeValue, cZjbCode);



                            if (cCheckRe == "0")
                            {
                                //  目标库没有单据， 新增的处理或者修改
                                if (cOperationValue == "0")
                                {
                                    //构建 过滤条件，唯一单据的pk
                                    string cFilter = string.Format(" a.{0}='{1}' ", cZjbPK, cZjbPKValue);
                                    //每次插入的具体数据             
                                    sql = string.Format("select {0} {1} where {2}", cSourcCol, cFrom, cFilter);
                                    dtIns = DbHelper2.Execute(sql, cConStrA).Tables[0];

                                    //取U8的id,如果是max的取法不一样
                                    if (cIdentity != "max" && !string.IsNullOrEmpty(cIdentity) && cIdentity != "")
                                    {
                                        //获得目标库的account ，账套号
                                        sql = " SELECT  dbo.fn_fd_getAccId()";
                                        string cAcc = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStrB));
                                        int[] iU8ID = DbHelper2.GetU8ID(cAcc, cIdentity, dtIns.Rows.Count, cConStrB);
                                        iId = iU8ID[0];
                                        iChild = iU8ID[1];
                                        iChild = iChild - dtIns.Rows.Count + 1;
                                    }
                                    else if (cIdentity == "max")
                                    {
                                        sql = string.Format("SELECT  MAX('{0}') FROM {1}", cPK, cTarTable1);
                                        iId = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql, cConStrB));
                                        iId = iId + 1;
                                        if (!string.IsNullOrEmpty(cTarTable2) || cTarTable2 != "")
                                        {
                                            sql = string.Format("SELECT  MAX('{0}') FROM {1}", cPKS, cTarTable2);
                                            iChild = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql, cConStrB));
                                            iChild = iId + 1;
                                        }
                                    }
                                    //第一行的id= iChild+iRowno-1；
                                    List<SqlParameter> listParas1 = new List<SqlParameter>();

                                    if (!string.IsNullOrEmpty(cIDname))
                                    {
                                        listParas1.Add(new SqlParameter("@id", iId));
                                    }
                                    //ccode,先临时赋值，生成成功后再update
                                    if (!string.IsNullOrEmpty(cCodeName))
                                    {
                                        listParas1.Add(new SqlParameter("@ccode", iId));
                                    }
                                    if (!string.IsNullOrEmpty(cSysCodeName))
                                    {
                                        listParas1.Add(new SqlParameter("@csysbarcode", iId));
                                    }
                                    SqlParameter[] paras1 = listParas1.ToArray();
                                    using (SqlConnection conn = new SqlConnection(cConStrB))
                                    {
                                        conn.Open();
                                        SqlTransaction sqlTransaction = conn.BeginTransaction();
                                        try
                                        {
                                            using (SqlCommand command = new SqlCommand("", conn))
                                            {

                                                ////创建临时表
                                                command.Transaction = sqlTransaction;
                                                //新增
                                                command.CommandText = createtempsqlIns;
                                                command.ExecuteNonQuery();
                                                //根据查询出的datatable创建临时表

                                                //插入新增
                                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, sqlTransaction))
                                                {
                                                    bulkcopy.BulkCopyTimeout = 300;
                                                    bulkcopy.DestinationTableName = tempTableIns;
                                                    bulkcopy.WriteToServer(dtIns);
                                                    bulkcopy.Close();
                                                }
                                                //新增序号
                                                sql = string.Format(@"ALTER  TABLE {0} ADD ibsxuhao  INT IDENTITY(1,1)", tempTableIns);

                                                command.CommandText = sql;
                                                command.ExecuteNonQuery();
                                                //新增

                                                int iRowno = 0;
                                                for (int i = 0; i < dtIns.Rows.Count; i++)
                                                {
                                                    iRowno = i + 1;

                                                    if (i == 0)
                                                    {
                                                        //插入主表
                                                        sql = string.Format(" insert into {0}({1}) select {2}  from {3} WHERE ibsxuhao =1", cTarTable1, cColumn1, cValue1, tempTableIns);
                                                        command.CommandText = sql;
                                                        if (paras1.Length > 0)
                                                        {
                                                            command.Parameters.AddRange(paras1);
                                                        }
                                                        command.ExecuteNonQuery();
                                                    }
                                                    if (!string.IsNullOrEmpty(cTarTable2) && cTarTable2 != "")
                                                    {
                                                        //构造子表的参数
                                                        List<SqlParameter> listParas2 = new List<SqlParameter>();
                                                        if (!string.IsNullOrEmpty(cIDname2))
                                                        {
                                                            listParas2.Add(new SqlParameter("@id", iId));
                                                        }
                                                        if (!string.IsNullOrEmpty(cCodeName2))
                                                        {
                                                            listParas2.Add(new SqlParameter("@ccode", iId));
                                                        }
                                                        if (!string.IsNullOrEmpty(cAutoIDname))
                                                        {
                                                            listParas2.Add(new SqlParameter("@autoid", iChild + i));
                                                        }
                                                        if (!string.IsNullOrEmpty(cRowNoName))
                                                        {
                                                            listParas2.Add(new SqlParameter("@irowno", iRowno));
                                                        }
                                                        if (!string.IsNullOrEmpty(cbSysCodeName))
                                                        {
                                                            listParas2.Add(new SqlParameter("@cbsysbarcode", iId));
                                                        }

                                                        SqlParameter[] paras2 = listParas2.ToArray();

                                                        sql = string.Format(" insert into {0}({1}) select {2} from  {3} where  ibsxuhao={4}",
                                                            cTarTable2, cColumn2, cValue2, tempTableIns, iRowno);
                                                        command.CommandText = sql;
                                                        if (paras2.Length > 0)
                                                        {
                                                            command.Parameters.Clear();
                                                            command.Parameters.AddRange(paras2);
                                                        }
                                                        command.ExecuteNonQuery();
                                                    }
                                                }

                                                string cGunalianRe = GuanLian(cTarTable1, iId, cZjbCodeValue, 1, sqlTransaction);
                                                if (cGunalianRe != "ok")
                                                {
                                                    //内层有try，可throw
                                                    throw new Exception("关联出错:" + cGunalianRe);
                                                }
                                                //如果没有@ccode,就不去获取
                                                if (!string.IsNullOrEmpty(cCodeName))
                                                {
                                                    GenVoucherNum genVoucherNum = new GenVoucherNum(cCardnumber, iId, cConStrB);
                                                    ccode2 = genVoucherNum.GetNum(sqlTransaction);
                                                    int iChongFu = 10;
                                                    sql = string.Format(@"SELECT  count(1)  FROM {0} where {1} = '{2}'", cTarTable1, cCodeName, ccode2);

                                                    int iCnt = DbHelper.GetDbInt(DbHelper2.GetSingleWithTrans(sql, sqlTransaction));
                                                    while (iCnt > 0 && iChongFu > 0)
                                                    {
                                                        //更新单据号码  ,10次还是取不到，报错                        
                                                        ccode2 = genVoucherNum.GetNum(sqlTransaction);
                                                        iChongFu--;
                                                        sql = string.Format(@"SELECT  count(1)  FROM {0} where {1} = '{2}'", cTarTable1, cCodeName, ccode2);
                                                        iCnt = DbHelper.GetDbInt(DbHelper2.GetSingleWithTrans(sql, sqlTransaction));
                                                    }
                                                    if (iChongFu == 0)
                                                    {
                                                        throw new Exception("获取单据编码错误！");
                                                    }
                                                }
                                                else
                                                {
                                                    //使用中间表的code
                                                    ccode2 = cZjbCodeValue;
                                                }
                                                //更新单据号
                                                sql = string.Format(" update {0} set {1}= '{2}' where  {3}='{4}'", cTarTable1, cCodeColumn, ccode2, cIDname, iId);
                                                DbHelper2.ExecuteSqlWithTrans(sql, sqlTransaction);
                                                //更新单据号,表体
                                                if (!string.IsNullOrEmpty(cCodeColumn2))
                                                {
                                                    sql = string.Format(" update {0} set {1}= '{2}' where  {3}='{4}'", cTarTable2, cCodeColumn2, ccode2, cIDname2, iId);
                                                    DbHelper2.ExecuteSqlWithTrans(sql, sqlTransaction);
                                                }
                                                if (!string.IsNullOrEmpty(cSysCodeName) && cSysCodeName != "")
                                                {
                                                    sql = string.Format(" update {0} set {1}= '{2}' where  {3}='{4}'", cTarTable1, cSysCodeName, "||" + cSysQianZhui + "|" + ccode2 + "|", cIDname, iId);
                                                    DbHelper2.ExecuteSqlWithTrans(sql, sqlTransaction);
                                                }
                                                if (!string.IsNullOrEmpty(cbSysCodeName) && cbSysCodeName != "")
                                                {
                                                    if (!string.IsNullOrEmpty(cRowNoName) && cRowNoName != "")
                                                        sql = string.Format(" update {0} set {1}= '{2}'+convert(varchar(10),{3}) where  {4}='{5}'", cTarTable2, cbSysCodeName, "||" + cSysQianZhui + "|" + ccode2 + "|", cRowNoName, cIDname, iId);
                                                    DbHelper2.ExecuteSqlWithTrans(sql, sqlTransaction);
                                                }


                                            }
                                            cRe += cZjbPKValue + "导入完成;";
                                            DbHelper2.CommitTransAndCloseConnection(sqlTransaction);
                                            sql = string.Format(" update {0} set cstatus=1 ,cupdateuser='U8',dupdatetime = getdate() from {0}  where {1}='{2}'  ",
                                            cZjbTable, cZjbPK, cZjbPKValue);
                                            DbHelper2.ExecuteNonQuery(sql, cConStrA);
                                        }
                                        catch (Exception ex)
                                        {
                                            DbHelper2.RollbackAndCloseConnection(sqlTransaction);
                                            cRe += cZjbPKValue + string.Format("{0};", ex.Message);
                                            ErrorRecord(cZjbPKValue, cLx, cOperationValue, cError);
                                            continue;
                                        }
                                        finally
                                        {
                                            conn.Close();
                                        }

                                    }

                                }
                                //删除的处理
                                else if (cOperationValue == "2")
                                {
                                    //目标库没有单据，所以不需要删除
                                    sql = string.Format(" update {0} set cstatus=1,cRemark='没有目标单据，无需删除'  from {0}  where {1}='{2}'  ",
                                         cZjbTable, cZjbPK, cZjbPKValue);
                                    DbHelper2.ExecuteNonQuery(sql, cConStrA);

                                    cRe += cZjbPKValue + "没有目标单据，无需删除;";
                                    ErrorRecord(cZjbPKValue, cLx, cOperationValue, "没有目标单据，无需删除!");

                                }
                            }
                            else if (cCheckRe == "1")
                            {
                                //  目标库没有单据， 新增的处理或者修改
                                if (cOperationValue == "0")
                                {
                                    //目标库没有单据，所以不需要删除
                                    sql = string.Format(" update {0} set cstatus=9,cRemark='已有相同编码，无需导入'  from {0}  where {1}='{2}'  ",
                                         cZjbTable, cZjbPK, cZjbPKValue);
                                    DbHelper2.ExecuteNonQuery(sql, cConStrA);
                                    cRe += cZjbPKValue + "已有相同编码，无需导入;";
                                    ErrorRecord(cZjbPKValue, cLx, cOperationValue, "已有相同编码，无需导入!");
                                }
                                else if (cOperationValue == "1")
                                {

                                    //插入到目标库
                                    //构建 过滤条件，唯一单据的pk
                                    string cFilter = string.Format(" a.{0}='{1}' ", cZjbPK, cZjbPKValue);
                                    //每次插入的具体数据             
                                    sql = string.Format("select {0} {1} where {2}", cSourcCol, cFrom, cFilter);
                                    dtIns = DbHelper2.Execute(sql, cConStrA).Tables[0];


                                    //查询源表和目标表的差异，insert表， delete 表和更新表
                                    using (SqlConnection conn = new SqlConnection(cConStrB))
                                    {
                                        conn.Open();
                                        SqlTransaction sqlTransaction = conn.BeginTransaction();
                                        try
                                        {
                                            using (SqlCommand command = new SqlCommand("", conn))
                                            {

                                                ////创建临时表
                                                command.Transaction = sqlTransaction;

                                                command.CommandText = createtempsqlIns;
                                                command.ExecuteNonQuery();
                                                //根据查询出的datatable创建临时表

                                                //插入新增
                                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, sqlTransaction))
                                                {
                                                    bulkcopy.BulkCopyTimeout = 300;
                                                    bulkcopy.DestinationTableName = tempTableIns;
                                                    bulkcopy.WriteToServer(dtIns);
                                                    bulkcopy.Close();
                                                }
                                                //新增序号
                                                sql = string.Format(@"ALTER  TABLE {0} ADD ibsxuhao  INT IDENTITY(1,1)", tempTableIns);

                                                command.CommandText = sql;
                                                command.ExecuteNonQuery();

                                                //表头的更新
                                                command.CommandText = string.Format(@" update b set {0} from {1} Tmp,{2} b where {3}", cUpdate1, tempTableIns, cTarTable1, cUpdateWhere);
                                                command.ExecuteNonQuery();

                                                if (!string.IsNullOrEmpty(cTarTable2) && cTarTable2 != "")
                                                {
                                                    //表体的更新
                                                    command.CommandText = string.Format(@" update b set {0} from {1} Tmp,{2} b,{3} a where a.{4}=b.{4} and  {5}",
                                                        cUpdate2, tempTableIns, cTarTable2, cTarTable1, cPK, cUpdateWhere);
                                                    command.ExecuteNonQuery();
                                                    //表体的删除,要有关联才可以判断
                                                    //                                                    command.CommandText = string.Format(@" delete  from  b from  so_sodetails b
                                                    //left join {0} Tmp  on  b.cDefine35=Tmp.id where b.id = {1} and Tmp.id is null  ", tempTableIns, iTarId);
                                                    //                                                    command.ExecuteNonQuery();


                                                    //                                                    //表体的新增
                                                    //                                                    command.CommandText = string.Format(@"select Tmp.*,0 as idpks,0 as idpk,row_number () over (order by Tmp.id) as rowid  into #TmpIns from {0} Tmp 
                                                    //left join so_sodetails b on  b.cDefine35 = Tmp.id and b.id = {1}
                                                    //                                where  b.isosid is null select @@rowcount", cTmpTable, iTarId);
                                                    //                                                    //查询新增行数
                                                    //                                                    int iCnt = DbHelper.GetDbInt(command.ExecuteScalar());


                                                    //                                                    if (iCnt != 0)
                                                    //                                                    {
                                                    //                                                        sql = " SELECT  dbo.fn_fd_getAccId()";
                                                    //                                                        string cAcc = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStrB));
                                                    //                                                        //取U8的id,如果是max的取法不一样
                                                    //                                                        if (cIdentity != "max")
                                                    //                                                        {
                                                    //                                                            int[] iU8ID = DbHelper2.GetU8ID(cAcc, cIdentity, iCnt, cConStrB);
                                                    //                                                            iId = iU8ID[0];
                                                    //                                                            iChild = iU8ID[1];
                                                    //                                                            iChild = iChild - iCnt + 1;
                                                    //                                                        }

                                                    //                                                        command.CommandText = string.Format(@" update Tmp set idpk={0},idpks=rowid+{1} from #TmpIns Tmp", iTarId, iChild);
                                                    //                                                        command.ExecuteNonQuery();
                                                    //                                                        cValue2 = cValue2.Replace("@id", "idpk").Replace("@autoid", "idpks");
                                                    //                                                        cValue2 = cValue2.Replace("@ccode", "idpk").Replace("@cbsysbarcode", "idpk").Replace("IPOIDS", "id").Replace("@irowno", "ivouchrowno");



                                                    //                                                        command.CommandText = string.Format(" insert into so_sodetails({0}) select {1} from  #TmpIns  ", cColumn2, cValue2);
                                                    //                                                        command.ExecuteNonQuery();

                                                    //                                                        command.CommandText = string.Format(" update b set csocode=a.csocode,cbSysBarCode='||SA17|'+a.csocode+'|'+b.irowno  from so_somain a,so_sodetails b where a.id= b.id and a.id = {0} ", iTarId);

                                                    //                                                        command.ExecuteNonQuery();

                                                }
                                            }






                                            cRe += cZjbPKValue + "更改完成;";
                                            DbHelper2.CommitTransAndCloseConnection(sqlTransaction);
                                            sql = string.Format(" update {0} set cstatus=1,cRemark='更改成功'  from {0}  where {1}='{2}'  ",
                                            cZjbTable, cZjbPK, cZjbPKValue);
                                            DbHelper2.ExecuteNonQuery(sql, cConStrA);

                                        }
                                        catch (Exception ex)
                                        {
                                            DbHelper2.RollbackAndCloseConnection(sqlTransaction);
                                            cRe += cZjbPKValue + ex.Message + ";";
                                            ErrorRecord(cZjbPKValue, cLx, cOperationValue, cError);
                                            continue;
                                        }
                                        finally
                                        {
                                            conn.Close();
                                        }
                                    }

                                }
                                //删除的处理
                                else if (cOperationValue == "2")
                                {
                                    sql = string.Format("select {0} from {1} where {2}='{3}'", cPK, cTarTable1, cCodeColumn, cZjbCodeValue);
                                    int iDelId = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql, cConStrB));
                                    //删除检查
                                    string cDelRe = DelCheck(cTarTable1, iDelId, 0);
                                    if (cDelRe != "ok")
                                    {
                                        throw new Exception("删除出错:" + cDelRe);
                                    }
                                    using (SqlConnection conn = new SqlConnection(cConStrB))
                                    {
                                        conn.Open();
                                        SqlTransaction sqlTransaction = conn.BeginTransaction();
                                        try
                                        {
                                            using (SqlCommand command = new SqlCommand("", conn))
                                            {

                                                ////创建临时表
                                                command.Transaction = sqlTransaction;
                                                string cGunalianRe = GuanLian(cLx, iId, cZjbCodeValue, -1, sqlTransaction);
                                                if (cGunalianRe != "ok")
                                                {
                                                    //内层有try，可throw
                                                    throw new Exception("删除关联出错:" + cGunalianRe);
                                                }
                                                //可以删除,根据code进行删除
                                                if (!string.IsNullOrEmpty(cTarTable2) && cTarTable2 != "")
                                                {
                                                    sql = string.Format(@" delete from b from {0} a,{1} b  where a.{2}=b.{3} and  a.{4}='{5}'", cTarTable1, cTarTable2, cPK, cIDname2, cZjbPKValue);
                                                    command.CommandText = sql;
                                                    command.ExecuteNonQuery();
                                                    //DbHelper2.ExecuteNonQuery(sql, cConStrB);
                                                }
                                                sql = string.Format(@" delete from {0} where {1}='{2}'", cTarTable1, cCodeColumn, cZjbCodeValue);
                                                command.CommandText = sql;
                                                command.ExecuteNonQuery();
                                                //DbHelper2.ExecuteNonQuery(sql, cConStrB);
                                            }
                                            cRe += cSourcePK + "删除完成;";
                                            DbHelper2.CommitTransAndCloseConnection(sqlTransaction);
                                            sql = string.Format(" update {0} set cstatus=1,cRemark='删除成功'  from {0}  where {1}='{2}'  ",
                                                    cZjbTable, cZjbPK, cZjbPKValue);
                                            DbHelper2.ExecuteNonQuery(sql, cConStrA);
                                        }
                                        catch (Exception ex)
                                        {
                                            DbHelper2.RollbackAndCloseConnection(sqlTransaction);
                                            ErrorRecord(cZjbPKValue, cLx, cOperationValue, ex.Message);
                                            cRe += cZjbPKValue + cZjbPKValue + "无法删除：" + ex.Message + ";";
                                            continue;
                                        }
                                        finally
                                        {
                                            conn.Close();
                                        }

                                    }
                                }

                            }

                        }
                    }

                }
                else
                {
                    throw new Exception(string.Format("error:没有配置此同步，无法更新!"));

                }
                return cRe;
            }
            catch (Exception ex)
            {
                return "error:" + ex.Message;
            }
        }
        #endregion

        #region  单据插入目标单据，按行进行插入
        /// <summary>
        /// /// <summary>
        /// U8单据，生成目标单据
        /// </summary>    
        /// </summary>
        /// <param name="cLx">接口类型</param>
        /// <param name="cfilter">过滤条件</param>
        /// <param name="idout">生成单据的id</param>
        /// <param name="ccode2">生成单据的ccode</param>
        /// <returns></returns>

        public string InsU8Voucher(string cLx, string cSourcePK,out int idout)
        {
            string cError = "";
            idout = 0;
            try
            {
                if (string.IsNullOrEmpty(cSourcePK))
                {
                    throw new Exception("没有单据号，无法协同！");
                    
                }

                //1 是主表  2 是子表
                string cRe = "";
                int iChild = 0;   //插入表的子表id
                int iId = 0;     //插入表的主表id             
                string sql = string.Format(" SELECT clx,cfrom,ctable,ctables,cidentity,czjbtable,PK,PKS,ctimestamp,cardnumber,csysqianzhui,czjbfilter,czjbpk,czjbcode,czjbtables,bgrade FROM dbo.zdy_inter_table WHERE CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cFrom = DbHelper.GetDbString(dt1.Rows[0]["cfrom"]);
                    //用于定位主键id
                    string cZjbFilter = DbHelper.GetDbString(dt1.Rows[0]["czjbfilter"]);
                    string cTarTable1 = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cTarTable2 = DbHelper.GetDbString(dt1.Rows[0]["ctables"]);
                    string cIdentity = DbHelper.GetDbString(dt1.Rows[0]["cidentity"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string cPKS = DbHelper.GetDbString(dt1.Rows[0]["PKS"]);
                    string cZjbPK = DbHelper.GetDbString(dt1.Rows[0]["czjbpk"]);   //中间表的主键 ，即编码
                    string cZjbCode = DbHelper.GetDbString(dt1.Rows[0]["czjbcode"]);   //中间表的单据编码，即编码
                    string cZjbTable = DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);  //中间表主表的数据
                    string cCardnumber = DbHelper.GetDbString(dt1.Rows[0]["cardnumber"]);
                    string cSysQianZhui = DbHelper.GetDbString(dt1.Rows[0]["csysqianzhui"]);
                    string ccode2 = "";
                    //用在主表查询上

                    if (!string.IsNullOrEmpty(cSourcePK) && cSourcePK != "")
                    {
                        if (string.IsNullOrEmpty(cZjbFilter))
                        {
                            cZjbFilter += " where 1=1 ";
                        }
                        cZjbFilter += string.Format(" and {0}='{1}' ", cZjbPK, cSourcePK);
                    }
                    //主表字段
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cvalue,'')<>'' and  ctable = '{1}' FOR XML PATH('')),1,1,'')", cLx, cTarTable1);
                    string cColumn1 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //子表字段
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cvalue,'')<>'' and  ctable = '{1}' FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cColumn2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //主表值
                    sql = string.Format(@"  SELECT STUFF((select ','+cvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cvalue,'')<>'' and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", cLx, cTarTable1);
                    string cValue1 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //子表值
                    sql = string.Format(@"  SELECT STUFF((select ','+cvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cvalue,'')<>'' and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cValue2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));


                     //主表和关联，子表的关联,取主键对应的字段. 删除时也会用到
                    //子表通过关联主表进行更新
                    string sqlcon = string.Format("SELECT  ccolumn FROM dbo.zdy_inter_tables WHERE clx='{0}' and ctable = '{1}' and  cvalue='{2}' ", cLx, cTarTable1, cZjbCode);
                    string cCodeColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sqlcon, cConStr));
                    //string cUpdateWhere = string.Format(" Tmp.{0} = b.{1}", cZjbCode, cCodeColumn);

                    string cCodeColumn2 = "";
                    if (!string.IsNullOrEmpty(cTarTable2) && cTarTable2 != "")
                    {
                        sqlcon = string.Format("SELECT  ccolumn FROM dbo.zdy_inter_tables WHERE clx='{0}' and ctable = '{1}' and  cvalue='{2}' ", cLx, cTarTable2, cZjbCode);
                        cCodeColumn2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sqlcon, cConStr));

                    }
                    //原始数据的插入栏目
                    //主表字段
                    sql = string.Format(@"  SELECT STUFF((select distinct ','+csourcecol from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(csourcecol,'')<>''   FOR XML PATH('')),1,1,'')", cLx, cTarTable1);
                    string cSourcCol = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //创建目标库临时表，目标库中有源表
                    var tempTableIns = @"#TempIns" + cTarTable1;
                    //var createtempsqlIns = string.Format("select {0} into {1} {2} where 1=2", cSourcCol, tempTableIns, cFrom);
                    sql = string.Format("select {0} {1} where 1=2", cSourcCol, cFrom);
                    DataTable dtIns = DbHelper2.Execute(sql, cConStrA).Tables[0];
                    var createtempsqlIns = CreateTable(dtIns.Columns, tempTableIns);

                    //取参数,单据号 
                    string cIDname = "", cIDname2 = "", cCodeName = "", cSysCodeName = "", cbSysCodeName = "", cCodeName2 = "";
                    string cAutoIDname = "", cRowNoName = "";


                    //id
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cvalue = '@id'", cLx, cTarTable1);
                    cIDname = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cvalue = '@ccode'", cLx, cTarTable1);
                    cCodeName = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //cSysCodeName
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cvalue = '@csysbarcode'", cLx, cTarTable1);
                    cSysCodeName = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //id2
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cvalue = '@id'", cLx, cTarTable2);
                    cIDname2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //ccode,先临时赋值，生成成功后再update
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cvalue = '@ccode'", cLx, cTarTable2);
                    cCodeName2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //autoid
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cvalue = '@autoid'", cLx, cTarTable2);
                    cAutoIDname = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //rowno
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cvalue = '@irowno'", cLx, cTarTable2);
                    cRowNoName = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //cbSysCodeName
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cvalue = '@cbsysbarcode'", cLx, cTarTable2);
                    cbSysCodeName = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));



                   
                                    //每次插入的具体数据             
                                    sql = string.Format("select {0} {1} {2}", cSourcCol, cFrom, cZjbFilter);
                                    dtIns = DbHelper2.Execute(sql, cConStrA).Tables[0];

                                    //取U8的id,如果是max的取法不一样
                                    if (cIdentity != "max" && !string.IsNullOrEmpty(cIdentity) && cIdentity != "")
                                    {
                                        //获得目标库的account ，账套号
                                        sql = " SELECT  dbo.fn_fd_getAccId()";
                                        string cAcc = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStrB));
                                        int[] iU8ID = DbHelper2.GetU8ID(cAcc, cIdentity, dtIns.Rows.Count, cConStrB);
                                        iId = iU8ID[0];
                                        iChild = iU8ID[1];
                                        iChild = iChild - dtIns.Rows.Count + 1;
                                    }
                                    else if (cIdentity == "max")
                                    {
                                        sql = string.Format("SELECT  MAX({0}) FROM {1}", cPK, cTarTable1);
                                        iId = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql, cConStrB));
                                        iId = iId + 1;
                                        if (!string.IsNullOrEmpty(cTarTable2) || cTarTable2 != "")
                                        {
                                            sql = string.Format("SELECT  MAX({0}) FROM {1}", cPKS, cTarTable2);
                                            iChild = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql, cConStrB));
                                            iChild = iChild + 1;
                                        }
                                    }
                                    //第一行的id= iChild+iRowno-1；
                                    List<SqlParameter> listParas1 = new List<SqlParameter>();

                                    if (!string.IsNullOrEmpty(cIDname))
                                    {
                                        listParas1.Add(new SqlParameter("@id", iId));
                                    }
                                    //ccode,先临时赋值，生成成功后再update
                                    if (!string.IsNullOrEmpty(cCodeName))
                                    {
                                        listParas1.Add(new SqlParameter("@ccode", iId));
                                    }
                                    if (!string.IsNullOrEmpty(cSysCodeName))
                                    {
                                        listParas1.Add(new SqlParameter("@csysbarcode", iId));
                                    }
                                    SqlParameter[] paras1 = listParas1.ToArray();
                        using (SqlConnection conn = new SqlConnection(cConStrB))
                        {
                            conn.Open();
                            SqlTransaction sqlTransaction = conn.BeginTransaction();
                            try
                            {
                                using (SqlCommand command = new SqlCommand("", conn))
                                {
                                    ////创建临时表
                                    command.Transaction = sqlTransaction;
                                    //新增
                                    command.CommandText = createtempsqlIns;
                                    command.ExecuteNonQuery();
                                    //根据查询出的datatable创建临时表

                                    //插入新增
                                    using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, sqlTransaction))
                                    {
                                        bulkcopy.BulkCopyTimeout = 300;
                                        bulkcopy.DestinationTableName = tempTableIns;
                                        bulkcopy.WriteToServer(dtIns);
                                        bulkcopy.Close();
                                    }
                                    //新增序号
                                    sql = string.Format(@"ALTER  TABLE {0} ADD ibsxuhao  INT IDENTITY(1,1)", tempTableIns);

                                    command.CommandText = sql;
                                    command.ExecuteNonQuery();
                                    //新增

                                    int iRowno = 0;
                                    for (int i = 0; i < dtIns.Rows.Count; i++)
                                    {
                                        iRowno = i + 1;

                                        if (i == 0)
                                        {
                                            //插入主表
                                            sql = string.Format(" insert into {0}({1}) select {2}  from {3} WHERE ibsxuhao =1", cTarTable1, cColumn1, cValue1, tempTableIns);
                                            command.CommandText = sql;
                                            if (paras1.Length > 0)
                                            {
                                                command.Parameters.AddRange(paras1);
                                            }
                                            command.ExecuteNonQuery();
                                        }
                                        if (!string.IsNullOrEmpty(cTarTable2) && cTarTable2 != "")
                                        {
                                            //构造子表的参数
                                            List<SqlParameter> listParas2 = new List<SqlParameter>();
                                            if (!string.IsNullOrEmpty(cIDname2))
                                            {
                                                listParas2.Add(new SqlParameter("@id", iId));
                                            }
                                            if (!string.IsNullOrEmpty(cCodeName2))
                                            {
                                                listParas2.Add(new SqlParameter("@ccode", iId));
                                            }
                                            if (!string.IsNullOrEmpty(cAutoIDname))
                                            {
                                                listParas2.Add(new SqlParameter("@autoid", iChild + i));
                                            }
                                            if (!string.IsNullOrEmpty(cRowNoName))
                                            {
                                                listParas2.Add(new SqlParameter("@irowno", iRowno));
                                            }
                                            if (!string.IsNullOrEmpty(cbSysCodeName))
                                            {
                                                listParas2.Add(new SqlParameter("@cbsysbarcode", iId));
                                            }

                                            SqlParameter[] paras2 = listParas2.ToArray();

                                            sql = string.Format(" insert into {0}({1}) select {2} from  {3} where  ibsxuhao={4}",
                                                cTarTable2, cColumn2, cValue2, tempTableIns, iRowno);
                                            command.CommandText = sql;
                                            if (paras2.Length > 0)
                                            {
                                                command.Parameters.Clear();
                                                command.Parameters.AddRange(paras2);
                                            }
                                            command.ExecuteNonQuery();
                                        }
                                    }

                                    string cGunalianRe = GuanLian(cTarTable1, iId, "", 1, sqlTransaction);
                                    if (cGunalianRe != "ok")
                                    {
                                        //内层有try，可throw
                                        throw new Exception("关联出错:" + cGunalianRe);
                                    }
                                    //如果没有@ccode,就不去获取
                                    if (!string.IsNullOrEmpty(cCodeName))
                                    {
                                        GenVoucherNum genVoucherNum = new GenVoucherNum(cCardnumber, iId, cConStrB);
                                        ccode2 = genVoucherNum.GetNum(sqlTransaction);
                                        int iChongFu = 10;
                                        sql = string.Format(@"SELECT  count(1)  FROM {0} where {1} = '{2}'", cTarTable1, cCodeName, ccode2);

                                        int iCnt = DbHelper.GetDbInt(DbHelper2.GetSingleWithTrans(sql, sqlTransaction));
                                        while (iCnt > 0 && iChongFu > 0)
                                        {
                                            //更新单据号码  ,10次还是取不到，报错                        
                                            ccode2 = genVoucherNum.GetNum(sqlTransaction);
                                            iChongFu--;
                                            sql = string.Format(@"SELECT  count(1)  FROM {0} where {1} = '{2}'", cTarTable1, cCodeName, ccode2);
                                            iCnt = DbHelper.GetDbInt(DbHelper2.GetSingleWithTrans(sql, sqlTransaction));
                                        }
                                        if (iChongFu == 0)
                                        {
                                            throw new Exception("获取单据编码错误！");
                                        }
                                    }
                                    else
                                    {
                                        //使用中间表的parentid
                                        ccode2 = cSourcePK;
                                    }
                                    //更新单据号
                                    sql = string.Format(" update {0} set {1}= '{2}' where  {3}='{4}'", cTarTable1, cCodeName, ccode2, cIDname, iId);
                                    DbHelper2.ExecuteSqlWithTrans(sql, sqlTransaction);
                                    //更新单据号,表体
                                    if (!string.IsNullOrEmpty(cCodeName2))
                                    {
                                        sql = string.Format(" update {0} set {1}= '{2}' where  {3}='{4}'", cTarTable2, cCodeName2, ccode2, cIDname2, iId);
                                        DbHelper2.ExecuteSqlWithTrans(sql, sqlTransaction);
                                    }
                                    if (!string.IsNullOrEmpty(cSysCodeName) && cSysCodeName != "")
                                    {
                                        sql = string.Format(" update {0} set {1}= '{2}' where  {3}='{4}'", cTarTable1, cSysCodeName, "||" + cSysQianZhui + "|" + ccode2 + "|", cIDname, iId);
                                        DbHelper2.ExecuteSqlWithTrans(sql, sqlTransaction);
                                    }
                                    if (!string.IsNullOrEmpty(cbSysCodeName) && cbSysCodeName != "")
                                    {
                                        if (!string.IsNullOrEmpty(cRowNoName) && cRowNoName != "")
                                            sql = string.Format(" update {0} set {1}= '{2}'+convert(varchar(10),{3}) where  {4}='{5}'", cTarTable2, cbSysCodeName, "||" + cSysQianZhui + "|" + ccode2 + "|", cRowNoName, cIDname, iId);
                                        DbHelper2.ExecuteSqlWithTrans(sql, sqlTransaction);
                                    }


                                }
                                cRe += cSourcePK + "导入完成;";
                            if (cLx == "XTA到货单")
                            {
                                sql = string.Format(" update {0} set istatus=1   where {1}='{2}'  ",
                                cZjbTable, cZjbPK, cSourcePK);
                                DbHelper2.ExecuteNonQuery(sql, cConStrA);
                            }
                            idout = iId;  //主键返回
                            DbHelper2.CommitTransAndCloseConnection(sqlTransaction);
                        }
                            catch (Exception ex)
                            {
                                DbHelper2.RollbackAndCloseConnection(sqlTransaction);
                                cRe += cSourcePK + string.Format("{0};", ex.Message);
                                ErrorRecord(cSourcePK, cLx, "1", cError);
                                
                            }
                            finally
                            {
                                conn.Close();
                            }

                         
                  
                           
                          

                       
                    }

                }
                else
                {
                    throw new Exception(string.Format("error:没有配置此同步，无法更新!"));

                }
                return cRe;
            }
            catch (Exception ex)
            {
                return "error:" + ex.Message;
            }
        }
        #endregion


        #region  检查是否已生单
        /// <summary>
        /// 检查单据是否已经生成,直接根据主键判断
        /// </summary>
        /// <param name="cLx"></param>
        /// <param name="cSoucceCode"></param>
        /// <returns>0: 不存在  1 ：存在 </returns>
        public string CheckGenNew(string cTable, string cZjbCodeValue, string cZjbCode)
        {
            
                string sql2 = string.Format(" select count(*) from  {1} where  {0}='{2}'", cZjbCode, cTable, cZjbCodeValue);
                string ccode = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql2, cConStrB));
                //根据目标单据进行判断，用扩展自定义项，生单不携带
                if (!string.IsNullOrEmpty(ccode) && ccode != "0")
                {
                    return "1";
                }
                else
                {
                    return "0";
                }
            
             



        }
        #endregion

        #region  检查是否已生单
        /// <summary>
        /// 检查单据是否已经生成
        /// </summary>
        /// <param name="cLx"></param>
        /// <param name="cSoucceCode"></param>
        /// <returns>0: 未生单  1 ：已生单  2： 未配置</returns>
        public string CheckGen(string cLx, string cZjbCodeValue, string cZjbCode)
        {
            string sql = string.Format("SELECT ctable,ccolumn FROM dbo.zdy_inter_tables WHERE clx='{1}' and  cvalue='{0}'", cZjbCode, cLx);
            DataTable dtCol = DbHelper2.ExecuteTable(sql, cConStr);
            if (dtCol.Rows.Count > 0)
            {
                string cTable = DbHelper.GetDbString(dtCol.Rows[0]["ctable"]);
                string cColumn = DbHelper.GetDbString(dtCol.Rows[0]["ccolumn"]);
                string sql2 = string.Format(" select count(*) from  {1} where  {0}='{2}'", cColumn, cTable, cZjbCodeValue);
                string ccode = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql2, cConStrB));
                //根据目标单据进行判断，用扩展自定义项，生单不携带
                if (!string.IsNullOrEmpty(ccode) && ccode != "0")
                {
                    return "1";
                }
                else
                {
                    return "0";
                }
            }
            else
            {
                return "2";
            }



        }
        #endregion


        /// <summary>
        /// 检查单据能否被删除
        /// </summary>
        /// <param name="cTable1">主表的名称 </param>
        /// <param name="cCode">主表的单据号</param>
        ///  <param name="iAutoid">子表的主键</param>
        /// <returns></returns>
        public string DelCheck(string cTable1 ,int iId,int iAutoid)
        {
            string sql = "";
            string sql2 = "";
            int iCnt = 0;
            switch (cTable1)
            {
                case "rdrecord01":
                    sql = string.Format(@"select count(*) from rdrecord01 a,rdrecords01 b where a.id= b.id
and a.id='{0}' ", iId);
                    if ( iAutoid != 0)
                    {
                        sql += string.Format(" and b.autoid={0}", iAutoid);
                    }
                    sql2 = sql + "  and isnull(b.isumbillquantity, 0) > 0";
                    iCnt =DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql2, cConStrB));
                    if (iCnt > 0)
                    {
                        return "单据已开票，无法删除！";
                    }
                    sql2 = sql + "  and isnull(cbaccounter, '') <> ''";
                    iCnt = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql2, cConStrB));
                    if (iCnt > 0)
                    {
                        return "单据已记账，无法删除！";
                    }

                    sql2 = sql + " and  isnull(dsdate, '') <> '' ";
                    iCnt = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql2, cConStrB));
                    if (iCnt > 0)
                    {
                        return "单据已结算，无法删除！";
                    }
                    break;
                case "rdrecord11":
                    sql = string.Format(@"select count(*) from rdrecord11 a,rdrecords11 b where a.id= b.id
and a.id='{0}' ", iId);
                    if (iAutoid != 0)
                    {
                        sql += string.Format(" and b.autoid={0}", iAutoid);
                    }
                    
                    sql2 = sql + "  and isnull(cbaccounter, '') <> ''";
                    iCnt = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql2, cConStrB));
                    if (iCnt > 0)
                    {
                        return "单据已记账，无法删除！";
                    }
                    break;
                case "rdrecord10":
                    sql = string.Format(@"select count(*) from rdrecord10 a,rdrecords10 b where a.id= b.id
and a.id='{0}' ", iId);
                    if (iAutoid != 0)
                    {
                        sql += string.Format(" and b.autoid={0}", iAutoid);
                    }

                    sql2 = sql + "  and isnull(cbaccounter, '') <> ''";
                    iCnt = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql2, cConStrB));
                    if (iCnt > 0)
                    {
                        return "单据已记账，无法删除！";
                    }
                    break;
                case "rdrecord32":
                    sql = string.Format(@"select count(*) from rdrecord32 a,rdrecords32 b where a.id= b.id
and a.id='{0}' ", iId);
                    if (iAutoid != 0)
                    {
                        sql += string.Format(" and b.autoid={0}", iAutoid);
                    }
                    sql2 = sql + "  and isnull(b.fsettleqty, 0) > 0";
                    iCnt = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql2, cConStrB));
                    if (iCnt > 0)
                    {
                        return "单据已开票，无法删除！";
                    }
                    
                   sql2 = sql + "  and isnull(cbaccounter, '') <> ''";
                    iCnt = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql2, cConStrB));
                    if (iCnt > 0)
                    {
                        return "单据已记账，无法删除！";
                    }
                    break;
                default:
                    break;


            }
            return "ok";
        }

        #region  插入U8业务单据到中间表
        /// <summary>
        ///插入U8业务单据到中间表，根据当前的dbid进行插入
        /// </summary>
        public string INSU8VouchToOther(string cLx, string cFilterOri,string dbid)
        {
            try
            {
                //CheckTimeTable();
               string cError = "";
                //查询同步的数据表及主键
                string sql = string.Format(@" SELECT  [id] ,[clx] ,[ctable],[ctables],czjbtable,[PK],CZJBFILTER,cdatecolumn,cdelfilter,ctimestamp,cfrom
  FROM [zdy_inter_table] WHERE  CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cTarTable = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cTarTables = DbHelper.GetDbString(dt1.Rows[0]["ctables"]);
                    string cZJBTable = DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string cFilter = DbHelper.GetDbString(dt1.Rows[0]["CZJBFILTER"]);
                    //取目标表数据的过滤,
                    ////20220303加账号的过滤
                    //string cFilterSrc = DbHelper.GetDbString(dt1.Rows[0]["cfrom"]);
                    //cFilterSrc += string.Format(@" and Tmp.sys_id ='{0}' ",dbid);
                    //取日期进行查询条件组合,对此协同的查询条件
                    //DateTime  dBeginDate  ;
                    sql = string.Format("SELECT DUPDATETIME,DBFILTER FROM ZDY_INTER_XIETONG WHERE dbid = '{0}' AND clx = '{1}'  ", dbid, cLx);
                    DataTable dtTime = DbHelper2.ExecuteTable(sql, cConStr);


                    if (dtTime.Rows.Count > 0)
                    {
                        string cDBFilter = DbHelper.GetDbString(dtTime.Rows[0]["DBFILTER"]);
                        if (!string.IsNullOrEmpty(cDBFilter))
                        {
                            cFilter += cDBFilter;
                        }
                        #region 不使用过滤条件
                        //dBeginDate = DbHelper.GetDbDate(DbHelper2.ExecuteScalar(sql, cConStr));
                        ////时间字段 及过滤条件
                        //string cDateCol = DbHelper.GetDbString(dt1.Rows[0]["cdatecolumn"]);
                        ////查询条件不为空，按传入的查询条件进行，如果没有按日期进行同步
                        //if (!string.IsNullOrEmpty(cFilterOri))
                        //{

                        //    cFilter += cFilterOri;
                        //}
                        //else
                        //{
                        //    if (!string.IsNullOrEmpty(cDateCol))
                        //    {
                        //        string[] cDateArray = cDateCol.Split('|');
                        //        foreach (string i in cDateArray)
                        //        {
                        //            //权限是5个主键，程序里直接定义
                        //            cFilter += string.Format(" and {0}>='{1}'", cDateCol, dBeginDate);
                        //        }
                        //    }
                        //}
                        #endregion

                    }
                    else
                    {
                        throw new Exception("没有定义此业务同步");
                    }
                    //主键的处理
                    string cRelation = "";
                    string cPKS = "";  //主键的组合
                    //更新语句用到的where条件
                    string[] cPKArray = cPK.Split('|');
                    foreach (string i in cPKArray)
                    {
                        //权限是5个主键，程序里直接定义
                        cRelation += string.Format(" and  Tmp.{0}=T.{0}  ", i);
                        cPKS += string.Format("Tmp.{0},", i);
                    }
                    cPKS = cPKS.Substring(0, cPKS.Length - 1);
                    ////20220303 增加sys_id
                    //cRelation += string.Format(" and  Tmp.sys_id='{0}'  ",dbid );


                    //时间戳的处理
                    string cTimestamp = DbHelper.GetDbString(dt1.Rows[0]["ctimestamp"]);
                    string cStampRelation = "";
                    string[] newPKAarray = new string[5];
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        //不足5位主键的补足5位，后面的补空‘’
                        for (int i = 0; i < 5; i++)
                        {
                            if (i < cPKArray.Length)
                            {
                                newPKAarray[i] = cPKArray[i];
                                if (i == 0)
                                {
                                    cStampRelation += string.Format(" and T.cpk=convert(varchar(50), Tmp.{0})", cPKArray[i]);
                                }
                                else
                                {
                                    cStampRelation += string.Format(" and T.cpk{0}=convert(varchar(50),Tmp.{1})", i + 1, cPKArray[i]);
                                }
                            }
                            else
                            {
                                newPKAarray[i] = "0";
                            }
                        }
                        //cStampRelation += string.Format(" and T.sys_id ='{0}' ", dbid);
                    }

                    //插入的字段和值
                    //插入的字段
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}' and ctable = '{1}' and isnull(cvalue,'')<>''
   FOR XML PATH('')),1,1,'')", cLx, cTarTable);
                    string cInsertColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                   



                    //插入的值
                    sql = string.Format(@"  SELECT STUFF((select ','+cvalue+' as '+ccolumn  from zdy_inter_tables WHERE clx = '{0}' and ctable = '{1}' and isnull(cvalue,'')<>''
   FOR XML PATH('')),1,1,'')", cLx, cTarTable);
                    string cSelectColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        cSelectColumn += string.Format(" ,convert(bigint,{0}) as {0}", cTimestamp);
                    }
                    //sys_id 替换
                    //cSelectColumn = cSelectColumn.Replace("@sys_id", dbid);

                    //删除过滤器
                    string cDelFilter = DbHelper.GetDbString(dt1.Rows[0]["cdelfilter"]);

                    //更新的字段
                    sql = string.Format(@"  SELECT STUFF((select ',T.'+ccolumn+'=Tmp.'+ccolumn from zdy_inter_tables WHERE clx = '{0}'
 and bupdate=1   FOR XML PATH('')),1,1,'')", cLx);
                    string cUpdateColunm = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //cUpdateColunm += " ,T.coperation='2' ";


                    //取目标表的id
                    //创建源库临时表，存放目标表的id
                    var srcTempTable = @"#TempSrc" + cTarTable;
                    //var srcCreatetempsql = string.Format("select {0},T.ctimestamp into {1} from {2} Tmp left join zdy_canrui_stamp T ON T.clx='{3}' where 1=2  ", cPKS, srcTempTable, cTarTable, cLx);
                    //获取取目标表的id,只取对应账号的
                    var srcselectsql = string.Format(@"select {0},isnull(T.ctimestamp,0) as ctimestamp from {1} Tmp left join zdy_canrui_stamp T ON T.clx='{2}' {3}   where  1=1   {4}",
                        cPKS, cTarTable, cLx, cStampRelation, cFilter);
                    DataTable srcdt = DbHelper2.ExecuteTable(srcselectsql, cConStrB);

                    var srcCreatetempsql = CreateTable(srcdt.Columns, srcTempTable);
                    //存储
                    DataTable dtIns = new DataTable();
                    DataTable dtDel = new DataTable();
                    DataTable dtUps = new DataTable();

                    //查询源表和目标表的差异，insert表， delete 表和更新表

                    using (SqlConnection conn = new SqlConnection(cConStrA))
                    {
                        using (SqlCommand command = new SqlCommand("", conn))
                        {
                            try
                            {
                                conn.Open();
                                //创建临时表
                                command.CommandText = srcCreatetempsql;
                                command.ExecuteNonQuery();
                                //插入源表数据
                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, null))
                                {
                                    bulkcopy.BulkCopyTimeout = 300;
                                    bulkcopy.DestinationTableName = srcTempTable;
                                    bulkcopy.WriteToServer(srcdt);
                                    bulkcopy.Close();
                                }

                                //获取源数据库的内容
                                var srcSelectsql = string.Format("select {0} from  {1} T  " +
                        "where not exists(select 1 from {2} Tmp where 1=1 {3}) {4}", cSelectColumn, cZJBTable, srcTempTable, cRelation, cFilter);
                                command.CommandText = srcSelectsql;
                                SqlDataAdapter adp = new SqlDataAdapter(command);
                                DataSet ds = new DataSet();
                                adp.Fill(ds);
                                dtIns = ds.Tables[0];

                                var srcDelectsql = string.Format("select {0} from  {1} Tmp  " +
                        "where not exists(select 1 from {2} T where 1=1 {3}) ", cPKS, srcTempTable, cZJBTable, cRelation);
                                command.CommandText = srcDelectsql;
                                adp = new SqlDataAdapter(command);
                                ds = new DataSet();
                                adp.Fill(ds);
                                dtDel = ds.Tables[0];

                                //单据更新状态下：有时间戳，才进行更新。
                                if (!string.IsNullOrEmpty(cTimestamp))
                                {
                                    var  srcUpdatesql = string.Format("select {0} from  {1} T  " +
                            "where exists(select 1 from {2} Tmp where convert(bigint,T.{4})<>Tmp.ctimestamp {3} ) ", cSelectColumn, cZJBTable, srcTempTable, cRelation, cTimestamp);
                                   
                                    command.CommandText = srcUpdatesql;
                                    adp = new SqlDataAdapter(command);
                                    ds = new DataSet();
                                    adp.Fill(ds);
                                    dtUps = ds.Tables[0];
                                }

                            }
                            catch (Exception ex)
                            {
                                throw new Exception(string.Format("error:{0}", ex.Message));
                            }
                            finally
                            {
                                conn.Close();
                            }
                        }
                    }

                    //更新目标库，插入、删除、更新

                    //创建目标库临时表
                    var tempTableIns = @"#TempIns" + cTarTable;
                    var createtempsqlIns = CreateTable(dtIns.Columns, tempTableIns);
                    var tempTableDel = @"#TempDel" + cTarTable;
                    var createtempsqlDel = CreateTable(dtDel.Columns, tempTableDel);
                    //插入目标表数据



                    var insertsql = string.Format(@"insert into {0}({1}) select {1} from  {2} Tmp  " +
                        "where not exists(select 1 from {0} T where 1=1 {3})  ;", cTarTable, cInsertColumn, tempTableIns, cRelation);


                    //insertsql = string.Format("SET IDENTITY_INSERT {0} ON  ", cTarTable) + insertsql + string.Format(" SET IDENTITY_Insert {0} OFF ", cTarTable);

                    //更新的语句
                    var tempTableUps = @"#TempUps" + cTarTable;
                    var createtempsqlUps = CreateTable(dtUps.Columns, tempTableUps);

                    var updatesql = string.Format("UPDATE T SET {0} FROM {1} T INNER JOIN {2} Tmp ON 1=1 {3} WHERE T.sys_id='{4}' ", cUpdateColunm, cTarTable, tempTableUps, cRelation,dbid);
                    //时间戳 ,如果存在，1影响更新。2保存到时间戳表.
                    //if (!string.IsNullOrEmpty(cTimestamp))
                    //{
                    //    updatesql += string.Format("  where   not exists(select 1 from zdy_canrui_stamp S where clx='{0}' and  isnull(S.ctimestamp,'0')=CONVERT(bigint,Tmp.{1}))", cLx, cTimestamp);

                    //}

                    var deletesql = "";
                    deletesql += string.Format(" delete From T from {0} T  where  exists(select 1 from {1} Tmp where  1=1 {2}) {3} ", cTarTable, tempTableDel, cRelation, cDelFilter);
                    var dropsql = string.Format("DROP TABLE {0}  DROP TABLE {1} DROP TABLE {2} ;", tempTableIns, tempTableDel, tempTableUps);
                   
                    using (SqlConnection conn = new SqlConnection(cConStrB))
                    {
                        using (SqlCommand command = new SqlCommand("", conn))
                        {
                            try
                            {
                                conn.Open();
                                //创建临时表
                                int updateQuery = 0;
                                //没有时间戳不更新
                                if (!string.IsNullOrEmpty(cTimestamp))
                                {
                                    command.CommandText = createtempsqlUps;
                                    command.ExecuteNonQuery();
                                    //更新,保留原identity值
                                    using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, null))
                                    {

                                        bulkcopy.BulkCopyTimeout = 300;
                                        bulkcopy.DestinationTableName = tempTableUps;
                                        bulkcopy.WriteToServer(dtUps);
                                        bulkcopy.Close();
                                    }


                                    //执行更新,有更新栏目的才进行更新
                                    command.CommandTimeout = 300;
                                    if (!string.IsNullOrEmpty(cUpdateColunm))
                                    {
                                        command.CommandText = updatesql;
                                        updateQuery = command.ExecuteNonQuery();
                                    }

                                }

                                //新增
                                command.CommandText = createtempsqlIns;
                                command.ExecuteNonQuery();
                                //插入新增
                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, null))
                                {
                                    bulkcopy.BulkCopyTimeout = 300;
                                    bulkcopy.DestinationTableName = tempTableIns;
                                    bulkcopy.WriteToServer(dtIns);
                                    bulkcopy.Close();
                                }

                                //执行新增
                                command.CommandText = insertsql;
                                int insertQuery = command.ExecuteNonQuery();


                                command.CommandText = createtempsqlDel;
                                command.ExecuteNonQuery();
                                //删除
                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, null))
                                {
                                    bulkcopy.BulkCopyTimeout = 300;
                                    bulkcopy.DestinationTableName = tempTableDel;
                                    bulkcopy.WriteToServer(dtDel);
                                    bulkcopy.Close();
                                }

                                //执行删除
                                command.CommandText = deletesql;
                                int delQuery = command.ExecuteNonQuery();

                                if (!string.IsNullOrEmpty(cTimestamp))
                                {
                                    //主键没有的插入
                                    command.CommandText = string.Format(@"INSERT INTO zdy_canrui_stamp(clx,cpk,cpk2,cpk3,cpk4,cpk5,ctimestamp,sys_id)
                                  select '{0}',{1},{2},{3},{4},{5},isnull({6},0),'{9}'  from {7} Tmp where not exists (select 1 from zdy_canrui_stamp T where 1=1 {8} and clx='{0}' ) ",
                                  cLx, newPKAarray[0], newPKAarray[1], newPKAarray[2], newPKAarray[3], newPKAarray[4], cTimestamp, tempTableIns, cStampRelation,dbid);
                                    command.ExecuteNonQuery();
                                    //相同的更新
                                    command.CommandText = string.Format(@"INSERT INTO zdy_canrui_stamp(clx,cpk,cpk2,cpk3,cpk4,cpk5,ctimestamp,sys_id)
                                  select '{0}',{1},{2},{3},{4},{5},isnull({6},0),'{9}'  from {7} Tmp where not exists (select 1 from zdy_canrui_stamp T where 1=1 {8} and clx='{0}' ) ",
                                   cLx, newPKAarray[0], newPKAarray[1], newPKAarray[2], newPKAarray[3], newPKAarray[4], cTimestamp, tempTableUps, cStampRelation, dbid);
                                    command.ExecuteNonQuery();

                                    command.CommandText = string.Format(@" update T set ctimestamp=Tmp.{0}
 from zdy_canrui_stamp T,{1} Tmp where T.clx='{2}' {3} and T.ctimestamp<>Tmp.{0}", cTimestamp, tempTableUps, cLx, cStampRelation);
                                    command.ExecuteNonQuery();

                                    //删除
                                    command.CommandText = string.Format(@" delete  from  T from zdy_canrui_stamp T,{1} Tmp where T.clx='{2}' {3} ", cTimestamp, tempTableDel, cLx, cStampRelation);
                                    command.ExecuteNonQuery();

                                }
                                cError = string.Format("更新{0}条数据，新增{1}条数据，删除{2}条数据", updateQuery, insertQuery, delQuery);
                            }
                            catch (Exception ex)
                            {
                                cError = string.Format("error:{0}", ex.Message);

                            }
                            finally
                            {
                                conn.Close();
                            }
                        }
                    }
                }
                else
                {
                    cError = "error:没有配置此档案同步";
                }

                SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@ctarcode",dbid),
                                         new SqlParameter("@CLX",cLx),
                                           new SqlParameter("@CREMARK",cError)

          };

                sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values(@ctarcode, getdate(),@CREMARK,@CLX)");
                DbHelper2.ExecuteNonQuery(sql, param, cConStr);

                if (cError.StartsWith("error"))
                {
                    return cError;
                }
                else
                {
                    return "ok:" + cError;
                }
            }
            catch (Exception ex)
            {
                //ErrorRecord(cZjbPKValue, cLx, cOperationValue, "没有目标单据，无需删除!");
                return "error:" + ex.Message;
                //WriteLog(cLx+"档案同步："+ex.Message);
            }
        }
        #endregion

        #region  插入U8业务单据到中间表，先删除再导入
        /// <summary>
        ///插入U8业务单据到中间表，根据当前的dbid进行插入
        /// </summary>
        public string INSU8VouchToOther2(string cLx, string cFilterOri, string dbid)
        {
            try
            {
                //CheckTimeTable();
                string cError = "";
                //查询同步的数据表及主键
                string sql = string.Format(@" SELECT  [id] ,[clx] ,[ctable],[ctables],czjbtable,[PK],CZJBFILTER,cdatecolumn,cdelfilter,ctimestamp,cfrom
  FROM [zdy_inter_table] WHERE  CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cTarTable = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cTarTables = DbHelper.GetDbString(dt1.Rows[0]["ctables"]);
                    string cZJBTable = DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string cFilter = DbHelper.GetDbString(dt1.Rows[0]["CZJBFILTER"]);
                    //取目标表数据的过滤,
                    ////20220303加账号的过滤
                    //string cFilterSrc = DbHelper.GetDbString(dt1.Rows[0]["cfrom"]);
                    //cFilterSrc += string.Format(@" and Tmp.sys_id ='{0}' ",dbid);
                    //取日期进行查询条件组合,对此协同的查询条件
                    //DateTime  dBeginDate  ;
                    sql = string.Format("SELECT DUPDATETIME,DBFILTER FROM ZDY_INTER_XIETONG WHERE dbid = '{0}' AND clx = '{1}'  ", dbid, cLx);
                    DataTable dtTime = DbHelper2.ExecuteTable(sql, cConStr);


                    if (dtTime.Rows.Count > 0)
                    {
                        string cDBFilter = DbHelper.GetDbString(dtTime.Rows[0]["DBFILTER"]);
                        if (!string.IsNullOrEmpty(cDBFilter))
                        {
                            cFilter += cDBFilter;
                        }
                    }
                    else
                    {
                        throw new Exception("没有定义此业务同步");
                    }
                    string cStampRelation = "";
                     
                     cStampRelation += string.Format(" and T.sys_id ='{0}' ", dbid);
                   

                    //插入的字段和值
                    //插入的字段
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}' and ctable = '{1}' and isnull(cvalue,'')<>''
   FOR XML PATH('')),1,1,'')", cLx, cTarTable);
                    string cInsertColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                     

                    //插入的值
                    sql = string.Format(@"  SELECT STUFF((select ','+cvalue+' as '+ccolumn  from zdy_inter_tables WHERE clx = '{0}' and ctable = '{1}' and isnull(cvalue,'')<>''
   FOR XML PATH('')),1,1,'')", cLx, cTarTable);
                    string cSelectColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                   
                    //sys_id 替换
                    cSelectColumn = cSelectColumn.Replace("@sys_id", dbid);

                    //读取insert表
                    DataTable dtIns = new DataTable();
                    //查询源表和目标表的差异，insert表 
                    using (SqlConnection conn = new SqlConnection(cConStrA))
                    {
                        using (SqlCommand command = new SqlCommand("", conn))
                        {
                            try
                            {
                                conn.Open();
                                //获取源数据库的内容
                                var srcSelectsql = string.Format("select {0} from  {1} T  ", cSelectColumn, cZJBTable);
                                command.CommandText = srcSelectsql;
                                SqlDataAdapter adp = new SqlDataAdapter(command);
                                DataSet ds = new DataSet();
                                adp.Fill(ds);
                                dtIns = ds.Tables[0];
                            }
                            catch (Exception ex)
                            {
                                throw new Exception(string.Format("error:{0}", ex.Message));
                            }
                            finally
                            {
                                conn.Close();
                            }
                        }
                    }

                    //更新目标库，插入、删除、更新

                    //创建目标库临时表
                    var tempTableIns = @"#TempIns" + cTarTable;
                    var createtempsqlIns = CreateTable(dtIns.Columns, tempTableIns);
                
                    var insertsql = string.Format(@"insert into {0}({1}) select {1} from  {2} Tmp  " , 
                        cTarTable, cInsertColumn, tempTableIns);


                    //insertsql = string.Format("SET IDENTITY_INSERT {0} ON  ", cTarTable) + insertsql + string.Format(" SET IDENTITY_Insert {0} OFF ", cTarTable);
 

                    var deletesql = "";
                    deletesql += string.Format(" delete From T from {0} T  where T.sys_id='{1}'  ", cTarTable,  dbid);
                    var dropsql = string.Format("DROP TABLE {0}  ;", tempTableIns  );

                    using (SqlConnection conn = new SqlConnection(cConStrB))
                    {
                        using (SqlCommand command = new SqlCommand("", conn))
                        {
                            try
                            {
                                conn.Open();
                                //创建临时表
                                int updateQuery = 0;
                                
                                //执行删除
                                command.CommandText = deletesql;
                                int delQuery = command.ExecuteNonQuery();
                                //新增
                                command.CommandText = createtempsqlIns;
                                command.ExecuteNonQuery();
                                //插入新增
                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, null))
                                {
                                    bulkcopy.BulkCopyTimeout = 300;
                                    bulkcopy.DestinationTableName = tempTableIns;
                                    bulkcopy.WriteToServer(dtIns);
                                    bulkcopy.Close();
                                }

                                //执行新增
                                command.CommandText = insertsql;
                                int insertQuery = command.ExecuteNonQuery();

                                cError = string.Format(" 删除{2}条数据，新增{1}条数据", updateQuery, insertQuery, delQuery);
                            }
                            catch (Exception ex)
                            {
                                cError = string.Format("error:{0}", ex.Message);

                            }
                            finally
                            {
                                conn.Close();
                            }
                        }
                    }
                }
                else
                {
                    cError = "error:没有配置此档案同步";
                }

                SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@ctarcode",dbid),
                                         new SqlParameter("@CLX",cLx),
                                           new SqlParameter("@CREMARK",cError)

          };

                sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values(@ctarcode, getdate(),@CREMARK,@CLX)");
                DbHelper2.ExecuteNonQuery(sql, param, cConStr);

                if (cError.StartsWith("error"))
                {
                    return cError;
                }
                else
                {
                    return "ok:" + cError;
                }
            }
            catch (Exception ex)
            {
                //ErrorRecord(cZjbPKValue, cLx, cOperationValue, "没有目标单据，无需删除!");
                return "error:" + ex.Message;
                //WriteLog(cLx+"档案同步："+ex.Message);
            }
        }
        #endregion

        #region  档案插入U8档案，全表协同，只有新增和更新
        /// <summary>
        /// 档案插入目标档案，全表协同，只有新增和更新
        /// </summary>
        /// <param name="cLx">业务类型</param> 
        /// <returns></returns>
        public string RecordToRecordNew(string cLx)
        {
            try
            {
                //CheckTimeTable();
               string  cError = "";
                //查询同步的数据表及主键,无时间戳
                string sql = string.Format(@" SELECT  [id] ,[clx] ,[ctable],czjbtable ,[PK], CZJBFILTER,cdatecolumn,cdelfilter,ctimestamp,czjbpk,bgrade
  FROM [zdy_inter_table] WHERE  CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cTarTable = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cSourceTable = DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string cZJBPK = DbHelper.GetDbString(dt1.Rows[0]["czjbpk"]);
                    int iGrade = DbHelper.GetDbInt(dt1.Rows[0]["bgrade"]);  //是否有级别
                    ////源表主键
                    //string cPK2 = DbHelper.GetDbString(dt1.Rows[0]["PKS"]);
                    string cRelation = " and " + DbHelper.GetDbString(dt1.Rows[0]["CZJBFILTER"]);
                    //根据账套号取数据，只取不是删除的
                    string cFilter = "  and  isnull(coperation,'')<>'3' ";
                    if (cLx != "XTA部门")
                    {
                        //删除的也传过去，字段上要加上 coperation
                        cFilter += string.Format(@" and sys_id='{0}'   ", cDBID.Substring(0,cDBID.IndexOf('-')));
                    }
                    //从源表查询的字段，如果时间戳不为空，增加时间戳                   
                    sql = string.Format(@"  SELECT STUFF((select ','+cvalue+' as '+ccolumn from zdy_inter_tables WHERE clx = '{0}' and isnull(cvalue,'')<>''
   FOR XML PATH('')),1,1,'')", cLx);
                    string cSelectColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    string cTimestamp = DbHelper.GetDbString(dt1.Rows[0]["ctimestamp"]);
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        //中间库使用固定的时间戳栏目
                        cSelectColumn += string.Format(" ,convert(bigint,ufts) as {0}, isnull(coperation,1) as coperation ", cTimestamp);
                    }
                    //删除过滤器
                    string cDelFilter = DbHelper.GetDbString(dt1.Rows[0]["cdelfilter"]);
                    //string cRelation = "";
                    ////更新语句用到的where条件
                    /// pk和zijpk必须是同等格式
                    
                    string[] cPKArray = cPK.Split('|');
                    foreach (string i in cPKArray)
                    {
                        //权限是5个主键，程序里直接定义
                        cRelation += string.Format(" and  Tmp.{0}=T.{0}  ", i);
                    }

                    //时间字段 及过滤条件, 多时间段也使用 | 进行分割
                    string cDateCol = DbHelper.GetDbString(dt1.Rows[0]["cdatecolumn"]);

                    if (!string.IsNullOrEmpty(cDateCol))
                    {
                        sql = string.Format("SELECT DUPDATETIME,DBFILTER FROM ZDY_INTER_XIETONG WHERE dbid ={0} AND clx = '{1}'  ", cDBID, cLx);
                        DateTime dDate = DbHelper.GetDbDate(DbHelper2.ExecuteScalar(sql, cConStr));
                        string[] cDateArray = cDateCol.Split('|');
                        foreach (string i in cDateArray)
                        {
                            //权限是5个主键，程序里直接定义
                            cFilter += string.Format(" and {0}>='{1}'", cDateCol, dDate);
                        }
                        //对账套的过滤设置
                        string cDBFilter = DbHelper.GetDbString(dt1.Rows[0]["DBFILTER"]);
                        if (!string.IsNullOrEmpty(cDBFilter))
                        {
                            cFilter += cDBFilter;
                        }
                    }

                    //插入的字段\值.. 中间表已经把value转换成目标值了。
                    //和单据的做法不一样，因为单据没有更新，没有多主键
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'  and isnull(cvalue,'')<>''
   FOR XML PATH('')),1,1,'')", cLx);
                    string cInsertColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));



                    //创建临时表的栏目
                    string cCreateColumn = cInsertColumn;
                    //创建临时表的
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        //增加 时间戳和coperation
                        cCreateColumn += string.Format(" ,{0}=cast(1 as bigint),'1' as coperation   ", cTimestamp);

                    }

                    //更新的字段
                    sql = string.Format(@"  SELECT STUFF((select ',T.'+ccolumn+'=Tmp.'+ccolumn from zdy_inter_tables WHERE clx = '{0}'
 and bupdate=1   FOR XML PATH('')),1,1,'')", cLx);
                    string cUpdateColunm = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //创建目标库临时表，根据要插入的进行创建
                    var tempTableName = @"#Temp" + cTarTable;
                    var createtempsql = string.Format("select {0} into {1} from {2} where 1=2", cCreateColumn, tempTableName, cTarTable);

                    //临时表插入目标表
                    var insertsql = string.Format(@"insert into {0}({1}) select {1} from  {2} Tmp  " +
                        "where not exists(select 1 from {0} T where 1=1 {3})  ;", cTarTable, cInsertColumn, tempTableName, cRelation);

                    //临时表更新目标表,  operation <>2,不是删除的
                    var updatesql = string.Format("UPDATE T SET {0} FROM {1} T INNER JOIN {2} Tmp ON 1=1 {3}   ", cUpdateColunm, cTarTable, tempTableName, cRelation);
                    //时间戳 ,如果存在，1影响更新。2保存到时间戳表.
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        updatesql += string.Format("  where   not exists(select 1 from zdy_zd_stamp S where clx='{0}' and  S.ctimestamp=CONVERT(bigint,Tmp.{1}))", cLx, cTimestamp);

                    }
                    var deletesql = "";
                    //删除的只取=2 的
                    //deletesql += string.Format(" delete From T from {0} T  where not exists(select 1 from {1} Tmp where  1=1 {2}) {3} ", cTarTable, tempTableName, cRelation, cDelFilter);
                    deletesql += string.Format(" delete From T from {0} T  where exists(select 1 from {1} Tmp where  1=1 {2} and Tmp.coperation='2') {3} ", cTarTable, tempTableName, cRelation, cDelFilter);
                    //加上时间戳，时间戳一样，代表之前处理过，本次不处理了
                    deletesql += string.Format("  where   not exists(select 1 from zdy_zd_stamp S where clx='{0}' and  S.ctimestamp=CONVERT(bigint,Tmp.{1}))", cLx, cTimestamp);

                    //删除临时表 
                    var dropsql = string.Format("DROP TABLE {0};", tempTableName);

                    //获取源数据库的内容
                    var selectsql = string.Format("select {0} from {1} where 1=1 {2}", cSelectColumn, cSourceTable, cFilter);
                    DataTable dt = DbHelper2.ExecuteTable(selectsql, cConStrA);





                    using (SqlConnection conn = new SqlConnection(cConStrB))
                    {
                        using (SqlCommand command = new SqlCommand("", conn))
                        {
                            try
                            {
                                conn.Open();
                                //创建临时表
                                command.CommandText = createtempsql;
                                command.ExecuteNonQuery();
                                //插入源表数据
                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                                {
                                    bulkcopy.BulkCopyTimeout = 300;
                                    bulkcopy.DestinationTableName = tempTableName;
                                    bulkcopy.WriteToServer(dt);
                                    bulkcopy.Close();
                                }
                                //执行更新,有更新栏目的才进行更新
                                command.CommandTimeout = 300;
                                int updateQuery = 0;
                                if (!string.IsNullOrEmpty(cUpdateColunm))
                                {
                                    command.CommandText = updatesql;
                                    updateQuery = command.ExecuteNonQuery();
                                }

                                //执行新增
                                command.CommandText = insertsql;
                                int insertQuery = command.ExecuteNonQuery();
                               

                               
                                //时间戳表的更新


                                //增加任务日志
                                //。。。。。。。。。。。。//

                                if (!string.IsNullOrEmpty(cTimestamp))
                                {
                                    string cStampRelation = "";
                                    //不足5位主键的补足5位，后面的补空‘’
                                    string[] newPKAarray = new string[5];
                                    for (int i = 0; i < 5; i++)
                                    {
                                        if (i < cPKArray.Length)
                                        {
                                            newPKAarray[i] = cPKArray[i];
                                            if (i == 0)
                                            {
                                                cStampRelation += string.Format(" and T.cpk=Tmp.{0}", cPKArray[i]);
                                            }
                                            else
                                            {
                                                cStampRelation += string.Format(" and T.cpk{0}=Tmp.{1}", i + 1, cPKArray[i]);
                                            }
                                        }
                                        else
                                        {
                                            newPKAarray[i] = "0";
                                        }
                                    }

                                    //设置新的关联关系


                                    //主键没有的插入
                                    command.CommandText = string.Format(@"INSERT INTO zdy_zd_stamp(clx,cpk,cpk2,cpk3,cpk4,cpk5,ctimestamp)
                                    select '{0}',{1},{2},{3},{4},{5},{6}  from {7} Tmp where not exists (select 1 from zdy_zd_stamp T where 1=1 {8} and clx='{0}') ",
                                        cLx, newPKAarray[0], newPKAarray[1], newPKAarray[2], newPKAarray[3], newPKAarray[4], cTimestamp, tempTableName, cStampRelation);
                                    command.ExecuteNonQuery();
                                    //相同的更新
                                    command.CommandText = string.Format(@" update T set ctimestamp=Tmp.{0}
 from zdy_zd_stamp T,{1} Tmp where T.clx='{2}' {3} and T.ctimestamp<>Tmp.{0}", cTimestamp, tempTableName, cLx, cStampRelation);
                                    command.ExecuteNonQuery();

                                    #region  根据主键插入，作废，更改成上面动态的
                                    //                                   {
                                    //                                       //主键没有的插入
                                    //                                       command.CommandText = string.Format(@"INSERT INTO zdy_zd_stamp(clx,cpk,cpk2,ctimestamp)
                                    //                                   select '{0}',{1},{4},{2}  from {3} Tmp where not exists (select 1 from zdy_zd_stamp T where t.cpk=Tmp.{1} and clx='{0}') ",
                                    //                                       cLx, cPK, cTimestamp, tempTableName, cPK2);
                                    //                                       command.ExecuteNonQuery();
                                    //                                       //相同的更新
                                    //                                       command.CommandText = string.Format(@" update T set ctimestamp=Tmp.{0},ddate= getdate()
                                    //from zdy_zd_stamp T,{1} Tmp where T.clx='{2}' and T.cpk=Tmp.{3} and t.cpk2=Tmp.{4} and T.ctimestamp<>Tmp.{0}", cTimestamp, tempTableName, cLx, cPK, cPK2);
                                    //                                       command.ExecuteNonQuery();

                                    //
                                    #endregion}
                                }
                                cError = string.Format("更新{0}条数据，新增{1}条数据 ;", updateQuery, insertQuery);
                            }
                            catch (Exception ex)
                            {
                                cError = string.Format("error:{0}", ex.Message);

                            }
                            finally
                            {
                                conn.Close();
                            }
                        }
                    }
                }
                else
                {
                    cError = "error:没有配置此档案同步";
                }

                SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@ctarcode",cDBID),
                                         new SqlParameter("@CLX",cLx),
                                           new SqlParameter("@CREMARK",cError)

          };

                sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values(@ctarcode, getdate(),@CREMARK,@CLX)");
                DbHelper2.ExecuteNonQuery(sql, param, cConStr);

                if (cError.StartsWith("error"))
                {
                    return cError;
                }
                else
                {
                    return "ok:" + cError;
                }
            }
            catch (Exception ex)
            {
                string sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values({2}, getdate(),'{0}','{1}')", ex.Message, cLx, cDBID);
                DbHelper2.ExecuteNonQuery(sql, cConStr);
                return "error:" + ex.Message;
                //WriteLog(cLx+"档案同步："+ex.Message);
            }
        }
        #endregion

        #region 插入档案的副表
        /// <summary>
        /// 插入档案的副表
        /// </summary>
        /// <param name="clx">业务类型</param>
        /// <returns></returns>
        public string InsFuBiao(string clx)
        {
            string sql = "";
            if (clx == "XTA存货档案")
            {
                //根据存货档案处理bas_part ，新增和删除
                sql = @"DECLARE @partid INT 
SELECT @partid = MAX(partid) FROM dbo.bas_part

INSERT INTO [bas_part]
           ([PartId]
           ,[InvCode]
           ,[Free1]
           ,[Free2]
           ,[Free3]
           ,[Free4]
           ,[Free5]
           ,[Free6]
           ,[Free7]
           ,[Free8]
           ,[Free9]
           ,[Free10] 
           ,[bVirtual] 
           ,[LLC]         
           ,[iSurenessType]       
           ,[iPercentumSum]
           ,[RoundingFactor]
           ,[FreeStockFlag])		    
SELECT @partid+ROW_NUMBER()OVER (ORDER BY a.cInvCode),  a.cInvCode,'','','',''
,'','','','','','',1,1,1,0,0,0 FROM inventory a
LEFT JOIN bas_part b ON a.cInvCode = b.InvCode 
WHERE b.InvCode IS NULL";

                DbHelper2.ExecuteNonQuery(sql, cConStrB);
            }
            else if (clx == "XTA供应商档案")
            {
                sql = @"INSERT INTO vendorbank(cVenCode, cBank, cBranch, cAccountNum, cAccountName, bDefault)
SELECT a.cVenCode,'01',a.cVenBank,cVenAccount,a.cVenName,1 FROM vendor a WHERE NOT EXISTS(SELECT 1 FROM vendorbank  b WHERE a.cVenCode = b.cVenCode
) AND ISNULL(cVenAccount,'')<> ''";
                DbHelper2.ExecuteNonQuery(sql, cConStrB);
            }


            return "ok";
        }
        #endregion

        #region  档案删除U8，按行执行
        /// <summary>
        /// 档案删除U8，按行执行
        /// </summary>
        /// <param name="cLx">业务类型</param> 
        /// <param name="delall">是否全部删除，Y：=2的全部删除，N，只删除时间戳中变动的</param> 
        /// <param name="ccode2"></param>
        /// <returns></returns>
        public string DelRecordNew(string cLx,string delall)
        {
            //测试 tran是否可以保留临时表
            //主子表的有存货档案、客户档案、供应商档案、人员档案等
            try
            {
                string cError = "";
                //1 是主表  2 是子表
                string cRe = "";           
                string sql = string.Format(" SELECT clx,cfrom,ctable,ctables,cidentity,czjbtable,PK,PKS,ctimestamp,cardnumber,csysqianzhui,czjbfilter,czjbpk,czjbcode,czjbtables,bgrade FROM dbo.zdy_inter_table WHERE CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cTarTable = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cSourceTable = DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string cZJBPK = DbHelper.GetDbString(dt1.Rows[0]["czjbpk"]);
                    int iGrade = DbHelper.GetDbInt(dt1.Rows[0]["bgrade"]);  //是否有级别
                    ////源表主键
                    //string cPK2 = DbHelper.GetDbString(dt1.Rows[0]["PKS"]);
                    string cRelation = " and " + DbHelper.GetDbString(dt1.Rows[0]["CZJBFILTER"]);
                    //根据账套号取数据，只取不是删除的
                    string cFilter = "  and  isnull(coperation,'')='3' ";
                    if (cLx != "XTA部门")
                    {
                        //删除的也传过去，字段上要加上 coperation
                        cFilter += string.Format(@" and sys_id='{0}'   ", cDBID.Substring(0, cDBID.IndexOf('-')));
                    }
                    //从源表查询的字段，如果时间戳不为空，增加时间戳                   
                    sql = string.Format(@"  SELECT STUFF((select ','+cvalue+' as '+ccolumn from zdy_inter_tables WHERE clx = '{0}' and isnull(cvalue,'')<>''
   FOR XML PATH('')),1,1,'')", cLx);
                    string cSelectColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    string cTimestamp = DbHelper.GetDbString(dt1.Rows[0]["ctimestamp"]);
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        //中间库使用固定的时间戳栏目
                        cSelectColumn += string.Format(" ,convert(bigint,ufts) as {0}, isnull(coperation,1) as coperation ", cTimestamp);
                    }
                    //删除过滤器
                    //string cDelFilter = DbHelper.GetDbString(dt1.Rows[0]["cdelfilter"]);
                    //string cRelation = "";
                    ////更新语句用到的where条件
                    /// pk和zijpk必须是同等格式

                    string[] cPKArray = cPK.Split('|');
                    foreach (string i in cPKArray)
                    {
                        //权限是5个主键，程序里直接定义
                        cRelation += string.Format(" and  Tmp.{0}=T.{0}  ", i);
                    }

                    ////时间字段 及过滤条件, 多时间段也使用 | 进行分割
                    //string cDateCol = DbHelper.GetDbString(dt1.Rows[0]["cdatecolumn"]);

                    //if (!string.IsNullOrEmpty(cDateCol))
                    //{
                    //    sql = string.Format("SELECT DUPDATETIME,DBFILTER FROM ZDY_INTER_XIETONG WHERE dbid ={0} AND clx = '{1}'  ", cDBID, cLx);
                    //    DateTime dDate = DbHelper.GetDbDate(DbHelper2.ExecuteScalar(sql, cConStr));
                    //    string[] cDateArray = cDateCol.Split('|');
                    //    foreach (string i in cDateArray)
                    //    {
                    //        //权限是5个主键，程序里直接定义
                    //        cFilter += string.Format(" and {0}>='{1}'", cDateCol, dDate);
                    //    }
                    //    //对账套的过滤设置
                    //    string cDBFilter = DbHelper.GetDbString(dt1.Rows[0]["DBFILTER"]);
                    //    if (!string.IsNullOrEmpty(cDBFilter))
                    //    {
                    //        cFilter += cDBFilter;
                    //    }
                    //}
                   
                    //获取源数据库的内容
                    var selectsql = string.Format("select {0} from {1} T where 1=1 {2}", cSelectColumn, cSourceTable, cFilter);
                    DataTable dt = DbHelper2.ExecuteTable(selectsql, cConStrA);
                   
                    
                    if (dt.Rows.Count == 0)
                    {
                      throw new Exception(string.Format("没有需要删除的档案！"));
                        
                    }
                    else
                    {
                        //循环，处理插入数据
                        for (int k = 0; k < dt.Rows.Count; k++)
                        {

                            string cZjbPKValue = DbHelper.GetDbString(dt.Rows[k][cPK]);
                            string cStampValue = DbHelper.GetDbString(dt.Rows[k][cTimestamp]);
                            //string cOperationValue = DbHelper.GetDbString(dt.Rows[k][cOperation]);

                            //需要只传 最新的删除， 新建临时表，然后后台关联时间戳
                            if (delall != "Y")
                            {
                                sql = string.Format(@"select ctimestamp from zdy_zd_stamp T where  clx='{0}' and cpk='{1}'", cLx, cZjbPKValue);
                                string cStampValue2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql,cConStrB));
                                if (string.IsNullOrEmpty(cStampValue2))
                                {
                                    sql = string.Format(@"insert into zdy_zd_stamp(clx,cpk,cpk2,cpk3,cpk4,cpk5,ctimestamp  
                                         values('{0}','{1}','0','0','0','0','{2}')", cLx, cZjbPKValue, "0");
                                    DbHelper2.ExecuteNonQuery(sql, cConStrB);
                                }
                                if (cStampValue== cStampValue2)
                                {
                                    continue;
                                }
                            }

                            //检查目标库是否存在
                            string cCheckRe = CheckGenNew(cTarTable, cZjbPKValue, cPK);
                            if (cCheckRe == "0")
                            { 
                               cRe += cZjbPKValue + "没有目标档案，无需删除;";
                            }
                            else if (cCheckRe == "1")
                            {
                                    try
                                    {
                                        //检查是否有下级
                                        if (iGrade == 1)
                                        {
                                            sql = string.Format("select count(*) from {0} where {1}<>'{2}' and {1} like '{2}%' ", cTarTable, cPK, cZjbPKValue);

                                            if (DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql, cConStrB)) > 0)
                                            {
                                                cRe += cZjbPKValue + "有下级分类，无法删除;";
                                                ErrorRecord(cZjbPKValue, cLx, "删除", "有下级分类，无法删除;");
                                              throw new Exception("有下级分类，无法删除;");
                                            }
                                        }

                                    //检查人员档案是否有业务员
                                    if (cLx=="XTA人员档案")
                                    {
                                        //先删除业务员
                                        //利用系统的检查判断是否用过
                                         string cDelTmpTblName2 = "tmpgly_" + DateTime.Now.ToString("yyMMddHHmmss");
                                        sql = string.Format(@"
                                                if exists(select 1 from tempdb..sysobjects where Id=Object_Id('{0}')  and type=N'U') 
                                                drop table tempdb..{0} ", cDelTmpTblName2);
                                        DbHelper2.ExecuteNonQuery(sql, cConStrB);
                                        sql = string.Format(@"EXECUTE ArchIsUsedProxy '{0}', '{1}', '{2}', 'cRetVal' 
                                                SELECT iStatus,cretval FROM tempdb..{2} 
                                                drop table tempdb..{2}",
                                            "person", cZjbPKValue, cDelTmpTblName2); 
                                        DataTable dtDel2 = DbHelper2.Execute(sql, cConStrB).Tables[0]; 


                                        if (DbHelper.GetDbInt(dtDel2.Rows[0][0]) < 0)
                                        {                                        
                                            sql = string.Format(@" delete from {0} where {1}='{2}'", "person", cPK, cZjbPKValue);
                                            DbHelper2.ExecuteNonQuery(sql, cConStrB);  
                                        }
                                        else
                                        {
                                            string cRet = DbHelper.GetDbString(dtDel2.Rows[0][1]);
                                            throw new Exception(cRet);
                                        }

                                    }

                                    //利用系统的检查判断是否用过
                                    string cDelTmpTblName = "tmpgly_" + DateTime.Now.ToString("yyMMddHHmmss");
                                        sql = string.Format(@"
                                                if exists(select 1 from tempdb..sysobjects where Id=Object_Id('{0}')  and type=N'U') 
                                                drop table tempdb..{0} ", cDelTmpTblName);
                                        DbHelper2.ExecuteNonQuery(sql, cConStrB);
                                        sql = string.Format(@"EXECUTE ArchIsUsedProxy '{0}', '{1}', '{2}', 'cRetVal' 
                                                SELECT iStatus,cretval FROM tempdb..{2} 
                                                drop table tempdb..{2}",
                                            cTarTable, cZjbPKValue, cDelTmpTblName);
                                        //DbHelper2.ExecuteNonQuery(sql, cConStrB);
                                        //sql = string.Format(@"SELECT iStatus,cretval FROM {0}", cDelTmpTblName);
                                        DataTable dtDel = DbHelper2.Execute(sql, cConStrB).Tables[0];
                                        //sql = string.Format(@"drop table tempdb..{0}", cDelTmpTblName);
                                        //DbHelper2.ExecuteNonQuery(sql, cConStrB);


                                        if (DbHelper.GetDbInt(dtDel.Rows[0][0]) < 0)
                                        {
                                        //可以删除
                                        //有子表的删除子表
                                        if (cLx == "XTA存货档案")
                                        {
                                            sql = string.Format(@" delete from bas_part where invcode='{0}'", cZjbPKValue);
                                            DbHelper2.ExecuteNonQuery(sql, cConStrB);
                                            sql = string.Format(@" delete from inventory_sub where cinvsubcode='{0}'", cZjbPKValue);
                                            DbHelper2.ExecuteNonQuery(sql, cConStrB);
                                        }
                                        else if (cLx == "XTA供应商档案")
                                        {
                                            sql = string.Format(@" delete from vendorbank where cvencode='{0}'", cZjbPKValue);
                                            DbHelper2.ExecuteNonQuery(sql, cConStrB); 
                                        }
                                        // else if (cLx == "XTA客户档案")
                                        //{
                                        //    sql = string.Format(@" delete from bas_part where invcode='{0}'", cZjbPKValue);
                                        //    DbHelper2.ExecuteNonQuery(sql, cConStrB);
                                        //    sql = string.Format(@" delete from inventory_sub where cinvsubcode='{0}'", cZjbPKValue);
                                        //    DbHelper2.ExecuteNonQuery(sql, cConStrB);
                                        //}

                                        sql = string.Format(@" delete from {0} where {1}='{2}'", cTarTable, cPK, cZjbPKValue);
                                            DbHelper2.ExecuteNonQuery(sql, cConStrB);

                                          
                                            cRe += cZjbPKValue + "删除完成;";
                                        }
                                        else
                                        {
                                            string cRet = DbHelper.GetDbString(dtDel.Rows[0][1]);
                                            ErrorRecord(cZjbPKValue, cLx, "删除", cRet);
                                            cRe += cZjbPKValue + "无法删除：" + cRet + ";";
                                        }
                                    }
                                    catch (Exception ex)
                                    {
                                        ErrorRecord(cZjbPKValue, cLx, "删除", ex.Message);
                                        cRe += cZjbPKValue + "无法删除：" + ex.Message + ";";
                                    }
                            }

                            //写时间戳                                        
                            string sqlstamp = string.Format(@" 
update zdy_zd_stamp set ctimestamp='{2}' where  clx='{0}'  and cpk='{1}' ",
                                           cLx, cZjbPKValue, DbHelper.GetDbString(dt.Rows[k][cTimestamp]));
                            DbHelper2.ExecuteNonQuery(sqlstamp, cConStrB);
                            continue;


                        }
                    }

                }
                else
                {
                    throw new Exception(string.Format("error:没有配置此同步，无法更新!"));

                }
                return cRe;
            }
            catch (Exception ex)
            {
                return "error:" + ex.Message;
            }
        }
        #endregion

       

        #region  档案插入目标档案，增删改执行，按行执行
        /// <summary>
        /// 生成业务单据
        /// </summary>
        /// <param name="cLx">业务类型</param>
        /// <param name="cSouceCode">原单据</param>
        /// <param name="cSourcePK">过滤的条件,传入的主键</param> 
        /// <param name="ccode2"></param>
        /// <returns></returns>
        public string RecordToRecord(string cLx, string cSourcePK)
        {
            //测试 tran是否可以保留临时表
            //主子表的有存货档案、客户档案、供应商档案、人员档案等
            try
            {
                string cError = "";
                //1 是主表  2 是子表
                string cRe = "";
                int iChild = 0;   //插入表的子表id
                int iId = 0;     //插入表的主表id             
                string sql = string.Format(" SELECT clx,cfrom,ctable,ctables,cidentity,czjbtable,PK,PKS,ctimestamp,cardnumber,csysqianzhui,czjbfilter,czjbpk,czjbcode,czjbtables,bgrade FROM dbo.zdy_inter_table WHERE CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {

                    string cFrom = DbHelper.GetDbString(dt1.Rows[0]["cfrom"]);
                    //用于定位主键id
                    string cZjbFilter = DbHelper.GetDbString(dt1.Rows[0]["czjbfilter"]);
                    string cTarTable1 = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cTarTable2 = DbHelper.GetDbString(dt1.Rows[0]["ctables"]);
                    string cIdentity = DbHelper.GetDbString(dt1.Rows[0]["cidentity"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    string cPKS = DbHelper.GetDbString(dt1.Rows[0]["PKS"]);
                    string cZjbPK = DbHelper.GetDbString(dt1.Rows[0]["czjbpk"]);   //中间表的主键 ，即编码
                    string cZjbCode = DbHelper.GetDbString(dt1.Rows[0]["czjbcode"]);   //中间表的主键 ，即编码
                    string cZjbTable = DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);  //中间表主表的数据
                    int iGrade = DbHelper.GetDbInt(dt1.Rows[0]["bgrade"]);
                    //用在主表查询上
                    if (!string.IsNullOrEmpty(cSourcePK) && cSourcePK != "")
                    {
                        cZjbFilter += string.Format(" and {0}='{1}' ", cZjbPK, cSourcePK);
                    }
                    //主表字段
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cvalue,'')<>'' and  ctable = '{1}' FOR XML PATH('')),1,1,'')", cLx, cTarTable1);
                    string cColumn1 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //子表字段
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cvalue,'')<>'' and  ctable = '{1}' FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cColumn2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //主表值
                    sql = string.Format(@"  SELECT STUFF((select ','+cvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cvalue,'')<>'' and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", cLx, cTarTable1);
                    string cValue1 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //子表值
                    sql = string.Format(@"  SELECT STUFF((select ','+cvalue from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cvalue,'')<>'' and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cValue2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //更新的主表字段
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn+'=Tmp.'+replace(replace(cvalue,'a.',''),'b.','') from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cvalue,'')<>''  and bupdate=1 and  ctable = '{1}' FOR XML PATH('')),1,1,'')", cLx, cTarTable1);
                    string cUpdate1 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //更新的子表字段
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn+'=Tmp.'+replace(replace(cvalue,'a.',''),'b.','') from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(cvalue,'')<>'' and bupdate=1 and  ctable = '{1}'  FOR XML PATH('')),1,1,'')", cLx, cTarTable2);
                    string cUpdate2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //主表和关联，子表的关联,取主键对应的字段. 删除时也会用到
                    string sqlcon = string.Format("SELECT  ccolumn FROM dbo.zdy_inter_tables WHERE clx='{0}' and ctable = '{1}' and  cvalue='{2}' ", cLx, cTarTable1, cZjbCode);
                    string cPKColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sqlcon, cConStr));
                    string cUpdateWhere = string.Format(" Tmp.{0} = b.{1}", cZjbCode, cPKColumn);
                    string cUpdateWhere2 = "";
                    string cPKColumn2 = "";
                    if (!string.IsNullOrEmpty(cTarTable2) && cTarTable2 != "")
                    {
                        sqlcon = string.Format("SELECT  ccolumn FROM dbo.zdy_inter_tables WHERE clx='{0}' and ctable = '{1}' and  cvalue='{2}' ", cLx, cTarTable2, cZjbCode);
                        cPKColumn2 = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sqlcon, cConStr));
                        cUpdateWhere2 = string.Format(" Tmp.{0} = b.{1}", cZjbCode, cPKColumn2);

                    }
                    //原始数据的插入栏目
                    //主表字段
                    sql = string.Format(@"  SELECT STUFF((select distinct ','+csourcecol from zdy_inter_tables WHERE clx = '{0}'
AND ISNULL(csourcecol,'')<>''   FOR XML PATH('')),1,1,'')", cLx, cTarTable1);
                    string cSourcCol = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //创建目标库临时表，目标库中有源表
                    var tempTableIns = @"#TempIns" + cTarTable1;
                    //var createtempsqlIns = string.Format("select {0} into {1} {2} where 1=2", cSourcCol, tempTableIns, cFrom);
                    sql = string.Format("select {0} {1} where 1=2", cSourcCol, cFrom);
                    DataTable dtIns = DbHelper2.Execute(sql, cConStrA).Tables[0];
                    var createtempsqlIns = CreateTable(dtIns.Columns, tempTableIns);

                    //取参数,单据号 
                    string cIDname = "";
                    string cAutoIDname = "";
                    //从系统获取的id或者autoid
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cvalue = '@id'", cLx, cTarTable2);
                    cIDname = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //autoid
                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cvalue = '@autoid'", cLx, cTarTable2);
                    cAutoIDname = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    //获得目标库的account ，账套号
                    sql = " SELECT  dbo.fn_fd_getAccId()";
                    string cAcc = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStrB));


                    //从中间表主表取数进行遍历
                    //int idoutNew = 0;
                    //string ccodeNew = "";
                    string sqlFilter = string.Format("select {0},{1},{2}  from {3} where {4}", cZjbPK, cOperation, cZjbCode,
                     cZjbTable, cZjbFilter);
                    DataTable dt = DbHelper2.ExecuteTable(sqlFilter, cConStrA);
                    if (dt.Rows.Count == 0)
                    {
                        if(string.IsNullOrEmpty(cSourcePK))
                        {
                            throw new Exception(string.Format("档案已全部协同！"));
                        }
                        else
                        {
                            throw new Exception(string.Format("档案已全部协同或编码未同步！"));
                        }

                    }
                    else
                    {
                        //循环，处理插入数据
                        for (int k = 0; k < dt.Rows.Count; k++)
                        {
                            string cZjbPKValue = DbHelper.GetDbString(dt.Rows[k][cZjbPK]);
                            string cZjbCodeValue = DbHelper.GetDbString(dt.Rows[k][cZjbCode]);
                            string cOperationValue = DbHelper.GetDbString(dt.Rows[k][cOperation]);
                            if (string.IsNullOrEmpty(cOperationValue) || cOperationValue == "")
                            {
                                cOperationValue = "0";
                            }
                            //检查目标库是否存在
                            string cCheckRe = CheckGen(cLx, cZjbCodeValue, cZjbCode);
                            if (cCheckRe == "0")
                            {
                                //  目标库没有单据， 新增的处理或者修改
                                if (cOperationValue == "0" || cOperationValue == "1")
                                {
                                    //构建 过滤条件，唯一单据的pk
                                    string cFilter = string.Format(" a.{0}='{1}' ", cZjbPK, cZjbPKValue);
                                    if (cLx != "XTA部门")
                                    {
                                        //删除的也传过去，字段上要加上 coperation
                                        cFilter += string.Format(@" and sys_id='{0}'   ", cDBID.Substring(0, cDBID.IndexOf('-')));
                                    }
                                    //每次插入的具体数据             
                                    sql = string.Format("select {0} {1} where {2}", cSourcCol, cFrom, cFilter);
                                    dtIns = DbHelper2.Execute(sql, cConStrA).Tables[0];

                                    //取U8的id,如果是max的取法不一样
                                    if (cIdentity != "max" && !string.IsNullOrEmpty(cIdentity) && cIdentity != "")
                                    {
                                        int[] iU8ID = DbHelper2.GetU8ID(cAcc, cIdentity, dtIns.Rows.Count, cConStrB);
                                        iId = iU8ID[0];
                                        iChild = iU8ID[1];
                                        iChild = iChild - dtIns.Rows.Count + 1;
                                    }
                                    else if (cIdentity == "max")
                                    {
                                        sql = string.Format("SELECT  MAX('{0}') FROM {1}", cPK, cTarTable1);
                                        iId = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql, cConStrB));
                                        iId = iId + 1;
                                        if (!string.IsNullOrEmpty(cTarTable2) || cTarTable2 != "")
                                        {
                                            sql = string.Format("SELECT  MAX('{0}') FROM {1}", cPKS, cTarTable2);
                                            iChild = DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql, cConStrB));
                                            iChild = iId + 1;
                                        }
                                    }
                                    //第一行的id= iChild+iRowno-1；
                                    List<SqlParameter> listParas1 = new List<SqlParameter>();
                                    //id
                                    sql = string.Format("SELECT ccolumn  FROM dbo.zdy_inter_tables WHERE clx = '{0}' and ctable ='{1}' AND cvalue = '@id'", cLx, cTarTable1);
                                    cIDname = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                                    if (!string.IsNullOrEmpty(cIDname))
                                    {
                                        listParas1.Add(new SqlParameter("@id", iId));
                                    }

                                    SqlParameter[] paras1 = listParas1.ToArray();
                                    using (SqlConnection conn = new SqlConnection(cConStrB))
                                    {
                                        conn.Open();
                                        SqlTransaction sqlTransaction = conn.BeginTransaction();
                                        try
                                        {
                                            using (SqlCommand command = new SqlCommand("", conn))
                                            {

                                                ////创建临时表
                                                command.Transaction = sqlTransaction;
                                                //新增
                                                command.CommandText = createtempsqlIns;
                                                command.ExecuteNonQuery();
                                                //根据查询出的datatable创建临时表

                                                //插入新增
                                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, sqlTransaction))
                                                {
                                                    bulkcopy.BulkCopyTimeout = 300;
                                                    bulkcopy.DestinationTableName = tempTableIns;
                                                    bulkcopy.WriteToServer(dtIns);
                                                    bulkcopy.Close();
                                                }
                                                //新增序号
                                                sql = string.Format(@"ALTER  TABLE {0} ADD ibsxuhao  INT IDENTITY(1,1)", tempTableIns);

                                                command.CommandText = sql;
                                                command.ExecuteNonQuery();
                                                //新增

                                                int iRowno = 0;
                                                for (int i = 0; i < dtIns.Rows.Count; i++)
                                                {
                                                    iRowno = i + 1;

                                                    if (i == 0)
                                                    {
                                                        //插入主表
                                                        sql = string.Format(" insert into {0}({1}) select {2}  from {3} WHERE ibsxuhao =1", cTarTable1, cColumn1, cValue1, tempTableIns);
                                                        command.CommandText = sql;
                                                        if (paras1.Length > 0)
                                                        {
                                                            command.Parameters.AddRange(paras1);
                                                        }
                                                        command.ExecuteNonQuery();
                                                    }
                                                    if (!string.IsNullOrEmpty(cTarTable2) && cTarTable2 != "")
                                                    {
                                                        //构造子表的参数
                                                        List<SqlParameter> listParas2 = new List<SqlParameter>();
                                                        if (!string.IsNullOrEmpty(cIDname))
                                                        {
                                                            listParas2.Add(new SqlParameter("@id", iId));
                                                        }
                                                        if (!string.IsNullOrEmpty(cAutoIDname))
                                                        {
                                                            listParas2.Add(new SqlParameter("@autoid", iChild + iRowno - 1));
                                                        }

                                                        SqlParameter[] paras2 = listParas2.ToArray();

                                                        sql = string.Format(" insert into {0}({1}) select {2} from  {3} where  ibsxuhao={4}",
                                                            cTarTable2, cColumn2, cValue2, tempTableIns, iRowno);
                                                        command.CommandText = sql;
                                                        if (paras2.Length > 0)
                                                        {
                                                            command.Parameters.Clear();
                                                            command.Parameters.AddRange(paras2);
                                                        }
                                                        command.ExecuteNonQuery();
                                                    }
                                                }


                                            }
                                            cRe += cZjbPKValue + "导入完成;";
                                            DbHelper2.CommitTransAndCloseConnection(sqlTransaction);
                                            sql = string.Format(" update {0} set cstatus=1,cRemark='导入成功'  from {0}  where {1}='{2}'  ",
                                            cZjbTable, cZjbPK, cZjbPKValue);
                                            DbHelper2.ExecuteNonQuery(sql, cConStrA);
                                        }
                                        catch (Exception ex)
                                        {
                                            DbHelper2.RollbackAndCloseConnection(sqlTransaction);
                                            cRe += cZjbPKValue+string.Format("{0};", ex.Message);
                                            ErrorRecord(cZjbPKValue, cLx, cOperationValue, cError);
                                            continue;
                                        }
                                        finally
                                        {
                                            conn.Close();
                                        }

                                    }

                                }
                                //删除的处理
                                else if (cOperationValue == "2")
                                {
                                    //目标库没有单据，所以不需要删除
                                    sql = string.Format(" update {0} set cstatus=1,cRemark='没有目标单据，无需删除'  from {0}  where {1}='{2}'  ",
                                         cZjbTable, cZjbPK, cZjbPKValue);
                                    DbHelper2.ExecuteNonQuery(sql, cConStrA);

                                    cRe += cZjbPKValue + "没有目标单据，无需删除;";
                                    ErrorRecord(cZjbPKValue,cLx, cOperationValue,  "没有目标单据，无需删除!");

                                }
                            }
                            else if (cCheckRe == "1")
                            {
                                //  目标库没有单据， 新增的处理或者修改
                                if (cOperationValue == "0")
                                {
                                    //目标库没有单据，所以不需要删除
                                    sql = string.Format(" update {0} set cstatus=9,cRemark='已有相同编码，无需导入'  from {0}  where {1}='{2}'  ",
                                         cZjbTable, cZjbPK, cZjbPKValue);
                                    DbHelper2.ExecuteNonQuery(sql, cConStrA);
                                    cRe += cZjbPKValue + "已有相同编码，无需导入;";
                                    ErrorRecord(cZjbPKValue, cLx, cOperationValue, "已有相同编码，无需导入!");
                                }
                                else if (cOperationValue == "1")
                                {

                                    //插入到目标库
                                    //构建 过滤条件，唯一单据的pk
                                    string cFilter = string.Format(" a.{0}='{1}' ", cZjbPK, cZjbPKValue);
                                    //每次插入的具体数据             
                                    sql = string.Format("select {0} {1} where {2}", cSourcCol, cFrom, cFilter);
                                    dtIns = DbHelper2.Execute(sql, cConStrA).Tables[0];


                                    //查询源表和目标表的差异，insert表， delete 表和更新表
                                    using (SqlConnection conn = new SqlConnection(cConStrB))
                                    {
                                        conn.Open();
                                        SqlTransaction sqlTransaction = conn.BeginTransaction();
                                        try
                                        {
                                            using (SqlCommand command = new SqlCommand("", conn))
                                            {

                                                ////创建临时表
                                                command.Transaction = sqlTransaction;

                                                command.CommandText = createtempsqlIns;
                                                command.ExecuteNonQuery();
                                                //根据查询出的datatable创建临时表

                                                //插入新增
                                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, sqlTransaction))
                                                {
                                                    bulkcopy.BulkCopyTimeout = 300;
                                                    bulkcopy.DestinationTableName = tempTableIns;
                                                    bulkcopy.WriteToServer(dtIns);
                                                    bulkcopy.Close();
                                                }
                                                //新增序号
                                                //sql = string.Format(@"ALTER  TABLE {0} ADD ibsxuhao  INT IDENTITY(1,1)", tempTableIns);

                                                //command.CommandText = sql;
                                                //command.ExecuteNonQuery();

                                                //表头的更新
                                                command.CommandText = string.Format(@" update b set {0} from {1} Tmp,{2} b where {3}", cUpdate1, tempTableIns, cTarTable1, cUpdateWhere);
                                                command.ExecuteNonQuery();

                                                if (!string.IsNullOrEmpty(cTarTable2) && cTarTable2 != "")
                                                {
                                                    //表体的更新
                                                    command.CommandText = string.Format(@" update b set {0} from {1} Tmp,{2} b where {3}", cUpdate2, tempTableIns, cTarTable2, cUpdateWhere2);
                                                    command.ExecuteNonQuery();
                                                }
                                                cRe += cZjbPKValue + "更改完成;";
                                                DbHelper2.CommitTransAndCloseConnection(sqlTransaction);
                                                sql = string.Format(" update {0} set cstatus=1,cRemark='更改成功'  from {0}  where {1}='{2}'  ",
                                                cZjbTable, cZjbPK, cZjbPKValue);
                                                DbHelper2.ExecuteNonQuery(sql, cConStrA);
                                            }
                                        }
                                        catch (Exception ex)
                                        {
                                            DbHelper2.RollbackAndCloseConnection(sqlTransaction);
                                            cRe += cZjbPKValue + ex.Message + ";";
                                            ErrorRecord(cZjbPKValue, cLx, cOperationValue, cError);
                                            continue;
                                        }
                                        finally
                                        {
                                            conn.Close();
                                        }
                                    }

                                }
                                //删除的处理
                                else if (cOperationValue == "2")
                                {
                                    try
                                    {
                                        //检查是否有下级
                                        if (iGrade == 1)
                                        {
                                            sql = string.Format("select count(*) from {0} where {1}<>'{2}' and {1} like '{2}%' ", cTarTable1, cPKColumn, cZjbPKValue);

                                            if (DbHelper.GetDbInt(DbHelper2.ExecuteScalar(sql, cConStrB)) > 0)
                                            {
                                                cRe += cZjbPKValue + "有下级分类，无法删除;";
                                                ErrorRecord(cZjbPKValue, cLx, cOperationValue,  "有下级分类，无法删除;");
                                                sql = string.Format(" update {0} set cstatus=9,cRemark='有下级分类，无法删除'  from {0}  where {1}='{2}'  ",
                                             cZjbTable, cZjbPK, cZjbPKValue);
                                                DbHelper2.ExecuteNonQuery(sql, cConStrA);
                                                continue;
                                            }
                                        }
                                        //利用系统的检查判断是否用过
                                        string cDelTmpTblName = "tmpgly_" + DateTime.Now.ToString("yyMMddHHmmss");
                                        sql = string.Format(@"
                                                if exists(select 1 from tempdb..sysobjects where Id=Object_Id('{0}')  and type=N'U') 
                                                drop table tempdb..{0} ", cDelTmpTblName);
                                        DbHelper2.ExecuteNonQuery(sql, cConStrB);
                                        sql = string.Format(@"EXECUTE ArchIsUsedProxy '{0}', '{1}', '{2}', 'cRetVal' 
                                                SELECT iStatus,cretval FROM tempdb..{2} 
                                                drop table tempdb..{2}",
                                            cTarTable1, cZjbPKValue, cDelTmpTblName);
                                        //DbHelper2.ExecuteNonQuery(sql, cConStrB);
                                        //sql = string.Format(@"SELECT iStatus,cretval FROM {0}", cDelTmpTblName);
                                        DataTable dtDel = DbHelper2.Execute(sql, cConStrB).Tables[0];
                                        //sql = string.Format(@"drop table tempdb..{0}", cDelTmpTblName);
                                        //DbHelper2.ExecuteNonQuery(sql, cConStrB);


                                        if (DbHelper.GetDbInt(dtDel.Rows[0][0]) < 0)
                                        {
                                            //可以删除
                                            if (!string.IsNullOrEmpty(cTarTable2) && cTarTable2 != "")
                                            {
                                                sql = string.Format(@" delete from {0} where {1}='{2}'", cTarTable2, cPKColumn2, cZjbPKValue);
                                                DbHelper2.ExecuteNonQuery(sql, cConStrB);
                                            }
                                            sql = string.Format(@" delete from {0} where {1}='{2}'", cTarTable1, cPKColumn, cZjbPKValue);
                                            DbHelper2.ExecuteNonQuery(sql, cConStrB);

                                            sql = string.Format(" update {0} set cstatus=1,cRemark='删除成功'  from {0}  where {1}='{2}'  ",
                                               cZjbTable, cZjbPK, cZjbPKValue);
                                            DbHelper2.ExecuteNonQuery(sql, cConStrA);
                                            cRe += cZjbPKValue + "删除完成;";
                                        }
                                        else
                                        {
                                            string cRet = DbHelper.GetDbString(dtDel.Rows[0][1]);
                                            ErrorRecord(cZjbPKValue, cLx, cOperationValue, cRet);
                                            cRe += cZjbPKValue +  "无法删除：" + cRet + ";";
                                            sql = string.Format(" update {0} set cstatus=9,cRemark='{3}'  from {0}  where {1}='{2}'  ",
                                           cZjbTable, cZjbPK, cZjbPKValue, cRet);
                                            DbHelper2.ExecuteNonQuery(sql, cConStrA);
                                            continue;
                                           

                                        }

                                    }
                                    catch (Exception ex)
                                    {
                                        ErrorRecord(cZjbPKValue, cLx, cOperationValue, ex.Message);
                                        cRe += cZjbPKValue + cZjbPKValue + "无法删除：" + ex.Message + ";";
                                        continue;
                                    }

                                }

                            }

                        }
                    }

                }
                else 
                {
                    throw new Exception(string.Format("error:没有配置此同步，无法更新!"));

                }
                return cRe;
            }
            catch (Exception ex)
            {
                return "error:" + ex.Message;
            }
        }
        #endregion


        #region 根据datatable创建表的sql
        /// <summary>
        /// 根据datatable创建表的sql
        /// </summary>
        /// <param name="columns">列名</param>
        /// <param name="tableName">表名</param>
        /// <param name="primaryKeys">主键的列名</param>
        public string  CreateTable(System.Data.DataColumnCollection columns, string tableName)
        {
            StringBuilder sb = new StringBuilder();


            // ibsxuhao，自增列
            sb.Append("create table [" + tableName + "] (");
            foreach (DataColumn column in columns)
            {
                sb.Append(" [" + column.ColumnName + "] " + this.GetTableColumnType(column.DataType) + ",");
            }


            //if (primaryKeys == null || primaryKeys.Length < 1)
            //{
            //    //MessageBox.Show("主键不允许为空!");
            //    return;
            //}

            string sql = sb.ToString();
            sql = sql.TrimEnd(',');
            sql += ")";

            return sql;


        }

        private string GetTableColumnType(System.Type type)
        {
            string result = "varchar(255)";
            string sDbType = type.ToString();
            switch (sDbType)
            {
                case "System.String":
                    break;
                case "System.Int16":
                    result = "int";
                    break;
                case "System.Int32":
                    result = "int";
                    break;
                case "System.Int64":
                    result = "float";
                    break;
                case "System.Decimal":
                    result = "decimal(18,4)";
                    break;
                case "System.Double":
                    result = "decimal(18,4)";
                    break;
                case "System.DateTime":
                    result = "datetime";
                    break;
                default:
                    break;
            }
            return result;
        }
        #endregion

        /// <summary>
        /// 错误记录
        /// </summary>
        /// <param name="cCode">来源单据号，中间表pk</param>
        /// <param name="cLx">同步业务类型</param>
        /// <param name="cInterLx">操作类型</param>
        /// <param name="cError">错误内容</param>
        /// <returns></returns>
        public void ErrorRecord(string cCode, string cLx,string cInterLx,string cError)
        {
            SqlParameter []  param = new SqlParameter[]{
                new SqlParameter("@ctarcode", cDBID),
                 new SqlParameter("@ccode", cCode),
                 new SqlParameter("@CINTERLX", cInterLx),
                                         new SqlParameter("@CLX", cLx),
                                           new SqlParameter("@CREMARK", cError)

          };

   string sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ccode,ctarcode,CINTERLX, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values(@ccode,@ctarcode,@CINTERLX, getdate(),@CREMARK,@CLX)");
    DbHelper2.ExecuteNonQuery(sql, param, cConStr);
        
        }

        #region  插入基础档案，表不相同
        /// <summary>
        /// 基础档案,不考虑时间因素，考虑双主键和过滤条件
        /// ctable: 目标表。 czjbtable 来源表，
        /// pk，目标表主键，可多主键，|间隔   zjbpk：源表主键
        /// ctimestamp ，原表时间戳  cdatecolumn 源表时间列
        /// cdelfilter ： 目标表删除选项
        /// 表体： ccolumn，目标表列名，cvalue 源表列名，cdefaultvale 插入默认值
        /// </summary>
        public string INSXieTongDangAn(string cLx)
        {
            try
            {
                //CheckTimeTable();
               string  cError = "";
                //查询同步的数据表及主键,无时间戳
                string sql = string.Format(@" SELECT  [id] ,[clx] ,[ctable],czjbtable ,[PK],PKS,czjbpk,CZJBFILTER,cdatecolumn,cdelfilter,ctimestamp
  FROM [zdy_inter_table] WHERE  CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    string cTarTable = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cSourceTable = DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    //源表主键
                    string cPK2 = DbHelper.GetDbString(dt1.Rows[0]["czjbpk"]);
                    string cFilter = DbHelper.GetDbString(dt1.Rows[0]["CZJBFILTER"]);

                    //创建目标库临时表用： 查询的字段，如果时间戳不为空，增加时间戳                   
                    sql = string.Format(@"  SELECT STUFF((select ','+cvalue+' as '+ccolumn from zdy_inter_tables WHERE clx = '{0}' and ctable = '{1}' and budaoru=0
   FOR XML PATH('')),1,1,'')", cLx, cTarTable);
                    string cSelectColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));
                    string cTimestamp = DbHelper.GetDbString(dt1.Rows[0]["ctimestamp"]);
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        cSelectColumn += string.Format(" ,convert(bigint,{0}) as {0}", cTimestamp);
                    }
                   
                    //删除过滤器
                    string cDelFilter = DbHelper.GetDbString(dt1.Rows[0]["cdelfilter"]);
                    string cRelation = "";
                    //更新语句用到的where条件
                    string[] cPKArray = cPK.Split('|');
                    foreach (string i in cPKArray)
                    {
                        //权限是5个主键，程序里直接定义
                        cRelation += string.Format(" and  Tmp.{0}=T.{0}  ", i);
                    }

                    //时间字段 及过滤条件, 多时间段也使用 | 进行分割
                    string cDateCol = DbHelper.GetDbString(dt1.Rows[0]["cdatecolumn"]);

                    if (!string.IsNullOrEmpty(cDateCol))
                    {
                        sql = string.Format("SELECT DUPDATETIME,DBFILTER FROM ZDY_INTER_XIETONG WHERE dbid ='{0}' AND clx = '{1}'  ", cDBID, cLx);
                        DateTime dDate = DbHelper.GetDbDate(DbHelper2.ExecuteScalar(sql, cConStr));
                        string[] cDateArray = cDateCol.Split('|');
                        foreach (string i in cDateArray)
                        {
                            //权限是5个主键，程序里直接定义
                            cFilter += string.Format(" and {0}>='{1}'", cDateCol, dDate);
                        }
                        //对账套的过滤设置
                        string cDBFilter = DbHelper.GetDbString(dt1.Rows[0]["DBFILTER"]);
                        if (!string.IsNullOrEmpty(cDBFilter))
                        {
                            cFilter += cDBFilter;
                        }
                    }
                    //插入的字段和值
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}' and ctable = '{1}' and budaoru=0
   FOR XML PATH('')),1,1,'')", cLx, cTarTable);
                    string cInsertColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //创建临时表的栏目
                    string cCreateColumn = cInsertColumn;
                    //创建临时表的
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        cCreateColumn += string.Format(" ,{0}=cast(1 as bigint)   ", cTimestamp);

                    }

                    //更新的字段
                    sql = string.Format(@"  SELECT STUFF((select ',T.'+ccolumn+'=Tmp.'+ccolumn from zdy_inter_tables WHERE clx = '{0}'
 and bupdate=1   FOR XML PATH('')),1,1,'')", cLx);
                    string cUpdateColunm = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //创建目标库临时表，根据要插入的进行创建
                    var tempTableName = @"#Temp" + cTarTable;
                    var createtempsql = string.Format("select {0} into {1} from {2} where 1=2", cCreateColumn, tempTableName, cTarTable);

                    //临时表插入目标表
                    var insertsql = string.Format(@"insert into {0}({1}) select {1} from  {2} Tmp  " +
                        "where not exists(select 1 from {0} T where 1=1 {3})  ;", cTarTable, cInsertColumn, tempTableName, cRelation);

                    //临时表更新目标表
                    var updatesql = string.Format("UPDATE T SET {0} FROM {1} T INNER JOIN {2} Tmp ON 1=1 {3} ", cUpdateColunm, cTarTable, tempTableName, cRelation);
                    //时间戳 ,如果存在，1影响更新。2保存到时间戳表.
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        updatesql += string.Format("  where   not exists(select 1 from zdy_zd_stamp S where clx='{0}' and  S.ctimestamp=CONVERT(bigint,Tmp.{1}))", cLx, cTimestamp);

                    }
                    var deletesql = "";
                    deletesql += string.Format(" delete From T from {0} T  where not exists(select 1 from {1} Tmp where  1=1 {2}) {3} ", cTarTable, tempTableName, cRelation, cDelFilter);
                    //删除临时表 
                    var dropsql = string.Format("DROP TABLE {0};", tempTableName);

                    //获取源数据库的内容
                    var selectsql = string.Format("select {0} from {1} where 1=1 {2}", cSelectColumn, cSourceTable, cFilter);
                    DataTable dt = DbHelper2.ExecuteTable(selectsql, cConStrA);

                    using (SqlConnection conn = new SqlConnection(cConStrB))
                    {
                        using (SqlCommand command = new SqlCommand("", conn))
                        {
                            try
                            {
                                conn.Open();
                                //创建临时表
                                command.CommandText = createtempsql;
                                command.ExecuteNonQuery();
                                //插入源表数据
                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                                {
                                    bulkcopy.BulkCopyTimeout = 300;
                                    bulkcopy.DestinationTableName = tempTableName;
                                    bulkcopy.WriteToServer(dt);
                                    bulkcopy.Close();
                                }
                                //执行更新,有更新栏目的才进行更新
                                command.CommandTimeout = 300;
                                int updateQuery = 0;
                                if (!string.IsNullOrEmpty(cUpdateColunm))
                                {
                                    command.CommandText = updatesql;
                                    updateQuery = command.ExecuteNonQuery();
                                }

                                //执行新增
                                command.CommandText = insertsql;
                                int insertQuery = command.ExecuteNonQuery();
                                //执行删除
                                command.CommandText = deletesql;
                                int delQuery = command.ExecuteNonQuery();
                                //时间戳表的更新


                                if (!string.IsNullOrEmpty(cTimestamp))
                                {
                                    string cStampRelation = "";
                                    //不足5位主键的补足5位，后面的补空‘’
                                    string[] newPKAarray = new string[5];
                                    for (int i = 0; i < 5; i++)
                                    {
                                        if (i < cPKArray.Length)
                                        {
                                            newPKAarray[i] = cPKArray[i];
                                            if (i == 0)
                                            {
                                                cStampRelation += string.Format(" and T.cpk=Tmp.{0}", cPKArray[i]);
                                            }
                                            else
                                            {
                                                cStampRelation += string.Format(" and T.cpk{0}=Tmp.{1}", i + 1, cPKArray[i]);
                                            }
                                        }
                                        else
                                        {
                                            newPKAarray[i] = "0";
                                        }
                                    }

                                    //设置新的关联关系



                                    //主键没有的插入
                                    command.CommandText = string.Format(@"INSERT INTO zdy_zd_stamp(clx,cpk,cpk2,cpk3,cpk4,cpk5,ctimestamp)
                                    select '{0}',{1},{2},{3},{4},{5},{6}  from {7} Tmp where not exists (select 1 from zdy_zd_stamp T where 1=1 {8} and clx='{0}') ",
                                        cLx, newPKAarray[0], newPKAarray[1], newPKAarray[2], newPKAarray[3], newPKAarray[4], cTimestamp, tempTableName, cStampRelation);
                                    command.ExecuteNonQuery();
                                    //相同的更新
                                    command.CommandText = string.Format(@" update T set ctimestamp=Tmp.{0}
 from zdy_zd_stamp T,{1} Tmp where T.clx='{2}' {3} and T.ctimestamp<>Tmp.{0}", cTimestamp, tempTableName, cLx, cStampRelation);
                                    command.ExecuteNonQuery();

                                    #region  根据主键插入，作废，更改成上面动态的
                                    //                                   {
                                    //                                       //主键没有的插入
                                    //                                       command.CommandText = string.Format(@"INSERT INTO zdy_zd_stamp(clx,cpk,cpk2,ctimestamp)
                                    //                                   select '{0}',{1},{4},{2}  from {3} Tmp where not exists (select 1 from zdy_zd_stamp T where t.cpk=Tmp.{1} and clx='{0}') ",
                                    //                                       cLx, cPK, cTimestamp, tempTableName, cPK2);
                                    //                                       command.ExecuteNonQuery();
                                    //                                       //相同的更新
                                    //                                       command.CommandText = string.Format(@" update T set ctimestamp=Tmp.{0},ddate= getdate()
                                    //from zdy_zd_stamp T,{1} Tmp where T.clx='{2}' and T.cpk=Tmp.{3} and t.cpk2=Tmp.{4} and T.ctimestamp<>Tmp.{0}", cTimestamp, tempTableName, cLx, cPK, cPK2);
                                    //                                       command.ExecuteNonQuery();

                                    //
                                    #endregion}
                                }
                                cError = string.Format("更新{0}条数据，新增{1}条数据，删除{2}条数据", updateQuery, insertQuery, delQuery);
                            }
                            catch (Exception ex)
                            {
                                cError = string.Format("error:{0}", ex.Message);
                                throw new Exception(cError);
                            }
                            finally
                            {
                                conn.Close();
                            }
                        }
                    }
                }
                else
                {
                    cError = "error:没有配置此档案同步";
                }

                SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@ctarcode",cDBID),
                                         new SqlParameter("@CLX",cLx),
                                           new SqlParameter("@CREMARK",cError)

          };

                sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values(@ctarcode, getdate(),@CREMARK,@CLX)");
                DbHelper2.ExecuteNonQuery(sql, param, cConStr);

                if (cError.StartsWith("error"))
                {
                    return cError;
                }
                else
                {
                    return "ok:" + cError;
                }
            }
            catch (Exception ex)
            {
                SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@CREMARK",ex.Message),
                 new SqlParameter("@CLX",cLx),
                  new SqlParameter("@ctarcode",cDBID)

          };

                string sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values(@ctarcode, getdate(),@CREMARK,@CLX)");
                DbHelper2.ExecuteNonQuery(sql, param, cConStr);
                return "error:" + ex.Message;
                //WriteLog(cLx+"档案同步："+ex.Message);
            }
        }
        #endregion


        #region  插入需要转换的业务单据,业务协同
        /// <summary>
        ///业务单据同步
        /// </summary>
        public string INSXieTongVouch(string cLx, string cFilterOri)
        {
            try
            {
                //CheckTimeTable();
               string cError = "";
                //查询同步的数据表及主键,无时间戳
                string sql = string.Format(@" SELECT  [id] ,[clx] ,[ctable],czjbtable ,[PK],PKS,CZJBFILTER,cdatecolumn,cdelfilter,ctimestamp
  FROM [zdy_inter_table] WHERE  CLX = '{0}' ", cLx);
                DataTable dt1 = DbHelper2.Execute(sql, cConStr).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    //目标表，主键
                    string cTarTable = DbHelper.GetDbString(dt1.Rows[0]["ctable"]);
                    string cPK = DbHelper.GetDbString(dt1.Rows[0]["PK"]);
                    //来源库
                    string cSourceTable = DbHelper.GetDbString(dt1.Rows[0]["czjbtable"]);
                    //源表主键
                    string cPK2 = DbHelper.GetDbString(dt1.Rows[0]["PKS"]);


                    string cFilter = DbHelper.GetDbString(dt1.Rows[0]["CZJBFILTER"]);

                    //创建目标库临时表用： 查询的字段，如果时间戳不为空，增加时间戳                   
                    sql = string.Format(@"  SELECT STUFF((select ','+cvalue+' as '+ccolumn from zdy_inter_tables WHERE clx = '{0}' and ctable = '{1}' and budaoru=0
   FOR XML PATH('')),1,1,'')", cLx, cTarTable);
                    string cSelectColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    string cTimestamp = DbHelper.GetDbString(dt1.Rows[0]["ctimestamp"]);
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        cSelectColumn += string.Format(" ,convert(bigint,{0}) as {0}", cTimestamp);
                    }
                    //删除过滤器
                    string cDelFilter = DbHelper.GetDbString(dt1.Rows[0]["cdelfilter"]);
                    string cRelation = "";
                    //更新语句用到的where条件
                    string[] cPKArray = cPK.Split('|');
                    foreach (string i in cPKArray)
                    {
                        //权限是5个主键，程序里直接定义
                        cRelation += string.Format(" and  Tmp.{0}=T.{0}  ", i);
                    }

                    //时间字段 及过滤条件, 多时间段也使用 | 进行分割
                    string cDateCol = DbHelper.GetDbString(dt1.Rows[0]["cdatecolumn"]);

                    if (!string.IsNullOrEmpty(cDateCol))
                    {
                        sql = string.Format("SELECT DUPDATETIME,DBFILTER FROM ZDY_INTER_XIETONG WHERE dbid ='{0}' AND clx = '{1}'  ", cDBID, cLx);
                        DateTime dDate = DbHelper.GetDbDate(DbHelper2.ExecuteScalar(sql, cConStr));
                        string[] cDateArray = cDateCol.Split('|');
                        foreach (string i in cDateArray)
                        {
                            //权限是5个主键，程序里直接定义
                            cFilter += string.Format(" and {0}>='{1}'", cDateCol, dDate);
                        }
                        //对账套的过滤设置
                        string cDBFilter = DbHelper.GetDbString(dt1.Rows[0]["DBFILTER"]);
                        if (!string.IsNullOrEmpty(cDBFilter))
                        {
                            cFilter += cDBFilter;
                        }
                    }

                    //从目标库，临时表插入正式表，用到的字段和值
                    sql = string.Format(@"  SELECT STUFF((select ','+ccolumn from zdy_inter_tables WHERE clx = '{0}' and ctable = '{1}' and budaoru=0
   FOR XML PATH('')),1,1,'')", cLx, cTarTable);
                    string cInsertColumn = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //创建临时表的栏目
                    string cCreateColumn = cInsertColumn;
                    //创建临时表的
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        cCreateColumn += string.Format(" ,{0}=cast(1 as bigint)   ", cTimestamp);

                    }

                    //更新的字段
                    sql = string.Format(@"  SELECT STUFF((select ',T.'+ccolumn+'=Tmp.'+ccolumn from zdy_inter_tables WHERE clx = '{0}'
 and bupdate=1   FOR XML PATH('')),1,1,'')", cLx);
                    string cUpdateColunm = DbHelper.GetDbString(DbHelper2.ExecuteScalar(sql, cConStr));

                    //创建目标库临时表，根据要插入的进行创建
                    var tempTableName = @"#Temp" + cTarTable;
                    var createtempsql = string.Format("select {0} into {1} from {2} where 1=2", cCreateColumn, tempTableName, cTarTable);

                    //临时表插入目标表
                    var insertsql = string.Format(@"insert into {0}({1}) select {1} from  {2} Tmp  " +
                        "where not exists(select 1 from {0} T where 1=1 {3})  ;", cTarTable, cInsertColumn, tempTableName, cRelation);

                    //临时表更新目标表
                    var updatesql = string.Format("UPDATE T SET {0} FROM {1} T INNER JOIN {2} Tmp ON 1=1 {3} ", cUpdateColunm, cTarTable, tempTableName, cRelation);
                    //加OA 审核标记，未审核的
                    if (cLx == "OA采购订单")
                    {
                        updatesql += " where isnull(field0028,'')='' ";
                    }
                    else if (cLx == "OA领料申请单")
                    {
                        updatesql += " where isnull(field0004,'')='' ";
                    }

                    //时间戳 ,如果存在，1影响更新。2保存到时间戳表.
                    if (!string.IsNullOrEmpty(cTimestamp))
                    {
                        updatesql += string.Format("  where   not exists(select 1 from zdy_zd_stamp S where clx='{0}' and  S.ctimestamp=CONVERT(bigint,Tmp.{1}))", cLx, cTimestamp);

                    }
                    //本次项目部删除oa表
                    //var deletesql = "";
                    //deletesql += string.Format(" delete From T from {0} T  where not exists(select 1 from {1} Tmp where  1=1 {2}) {3} ", cTarTable, tempTableName, cRelation, cDelFilter);
                    //删除临时表 
                    var dropsql = string.Format("DROP TABLE {0};", tempTableName);

                    //获取源数据库的内容
                    var selectsql = string.Format("select {0} from {1} where 1=1 {2}", cSelectColumn, cSourceTable, cFilter);
                    DataTable dt = DbHelper2.ExecuteTable(selectsql, cConStrA);

                    using (SqlConnection conn = new SqlConnection(cConStrB))
                    {
                        using (SqlCommand command = new SqlCommand("", conn))
                        {
                            try
                            {
                                conn.Open();
                                //创建临时表
                                command.CommandText = createtempsql;
                                command.ExecuteNonQuery();
                                //插入源表数据
                                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                                {
                                    bulkcopy.BulkCopyTimeout = 300;
                                    bulkcopy.DestinationTableName = tempTableName;
                                    bulkcopy.WriteToServer(dt);
                                    bulkcopy.Close();
                                }
                                //执行更新,有更新栏目的才进行更新
                                command.CommandTimeout = 300;
                                int updateQuery = 0;
                                if (!string.IsNullOrEmpty(cUpdateColunm))
                                {
                                    command.CommandText = updatesql;
                                    updateQuery = command.ExecuteNonQuery();
                                }

                                //执行新增
                                command.CommandText = insertsql;
                                int insertQuery = command.ExecuteNonQuery();
                                ////执行删除
                                //command.CommandText = deletesql;
                                //int delQuery = command.ExecuteNonQuery();
                                //时间戳表的更新


                                if (!string.IsNullOrEmpty(cTimestamp))
                                {
                                    string cStampRelation = "";
                                    //不足5位主键的补足5位，后面的补空‘’
                                    string[] newPKAarray = new string[5];
                                    for (int i = 0; i < 5; i++)
                                    {
                                        if (i < cPKArray.Length)
                                        {
                                            newPKAarray[i] = cPKArray[i];
                                            if (i == 0)
                                            {
                                                cStampRelation += string.Format(" and T.cpk=Tmp.{0}", cPKArray[i]);
                                            }
                                            else
                                            {
                                                cStampRelation += string.Format(" and T.cpk{0}=Tmp.{1}", i + 1, cPKArray[i]);
                                            }
                                        }
                                        else
                                        {
                                            newPKAarray[i] = "0";
                                        }
                                    }

                                    //设置新的关联关系



                                    //主键没有的插入
                                    command.CommandText = string.Format(@"INSERT INTO zdy_zd_stamp(clx,cpk,cpk2,cpk3,cpk4,cpk5,ctimestamp)
                                    select '{0}',{1},{2},{3},{4},{5},{6}  from {7} Tmp where not exists (select 1 from zdy_zd_stamp T where 1=1 {8} and clx='{0}') ",
                                        cLx, newPKAarray[0], newPKAarray[1], newPKAarray[2], newPKAarray[3], newPKAarray[4], cTimestamp, tempTableName, cStampRelation);
                                    command.ExecuteNonQuery();
                                    //相同的更新
                                    command.CommandText = string.Format(@" update T set ctimestamp=Tmp.{0}
 from zdy_zd_stamp T,{1} Tmp where T.clx='{2}' {3} and T.ctimestamp<>Tmp.{0}", cTimestamp, tempTableName, cLx, cStampRelation);
                                    command.ExecuteNonQuery();

                                    #region  根据主键插入，作废，更改成上面动态的
                                    //                                   {
                                    //                                       //主键没有的插入
                                    //                                       command.CommandText = string.Format(@"INSERT INTO zdy_zd_stamp(clx,cpk,cpk2,ctimestamp)
                                    //                                   select '{0}',{1},{4},{2}  from {3} Tmp where not exists (select 1 from zdy_zd_stamp T where t.cpk=Tmp.{1} and clx='{0}') ",
                                    //                                       cLx, cPK, cTimestamp, tempTableName, cPK2);
                                    //                                       command.ExecuteNonQuery();
                                    //                                       //相同的更新
                                    //                                       command.CommandText = string.Format(@" update T set ctimestamp=Tmp.{0},ddate= getdate()
                                    //from zdy_zd_stamp T,{1} Tmp where T.clx='{2}' and T.cpk=Tmp.{3} and t.cpk2=Tmp.{4} and T.ctimestamp<>Tmp.{0}", cTimestamp, tempTableName, cLx, cPK, cPK2);
                                    //                                       command.ExecuteNonQuery();

                                    //
                                    #endregion}
                                }
                                cError = string.Format("更新{0}条数据，新增{1}条数据 ", updateQuery, insertQuery);
                            }
                            catch (Exception ex)
                            {
                                cError = string.Format("error:{0}", ex.Message);

                            }
                            finally
                            {
                                conn.Close();
                            }
                        }
                    }


                    //更新U8的标记，源数据库
                    using (SqlConnection conn = new SqlConnection(cConStrA))
                    {
                        using (SqlCommand command = new SqlCommand("", conn))
                        {
                            try
                            {
                                conn.Open();

                                //执行更新,有更新栏目的才进行更新
                                command.CommandTimeout = 300;
                                updatesql = "";
                                int updateQuery = 0;
                                if (cLx == "OA采购订单")
                                {
                                    updatesql = string.Format(@"update po_pomain set cdefine11='已提交' from po_pomain a,
                                    ({0}) b where a.cpoid = b.field0003   ", selectsql);

                                }
                                else if (cLx == "OA领料申请单")
                                {
                                    updatesql = string.Format(@"update materialappvouch set cdefine11='已提交' from materialappvouch a,
                                    ({0}) b where a.ccode = b.field0001   ", selectsql);

                                }
                                //回写OA的也在这

                                if (updatesql != "")
                                {
                                    command.CommandText = updatesql;
                                    updateQuery = command.ExecuteNonQuery();
                                }


                            }
                            catch (Exception ex)
                            {
                                cError += string.Format("回写U8错误，error:{0}", ex.Message);

                            }
                            finally
                            {
                                conn.Close();
                            }
                        }
                    }
                }
                else
                {
                    cError = "error:没有配置此档案同步";
                }

                SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@ctarcode",cDBID),
                                         new SqlParameter("@CLX",cLx),
                                           new SqlParameter("@CREMARK",cError)

          };

                sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values(@ctarcode, getdate(),@CREMARK,@CLX)");
                DbHelper2.ExecuteNonQuery(sql, param, cConStr);

                if (cError.StartsWith("error"))
                {
                    return cError;
                }
                else
                {

                    return "ok:" + cError;
                }
            }
            catch (Exception ex)
            {
                string sql = string.Format(@"
                    INSERT INTO[dbo].[ZDY_INTER_XIETONGLOG]
                    (ctarcode, [DUPDATETIME]
          ,[CREMARK]
          ,[CLX]) values('{2}', getdate(),'{0}','{1}')", ex.Message, cLx, cDBID);
                DbHelper2.ExecuteNonQuery(sql, cConStr);
                return "error:" + ex.Message;
                //WriteLog(cLx+"档案同步："+ex.Message);
            }




        }
        #endregion
        #region  生单之后的处理动作，回写关联表信息
        /// <summary>
        /// 生单之后的处理动作，回写关联表信息
        /// </summary>
        /// <param name="cLx">业务类型</param>
        /// <param name="iID">生成单据主键</param>
        ///  /// <param name="cZJBCode">中间表单据号</param>
        /// <param name="iDelFlag">删除标记，删除：-1.新增是 +1 </param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public string GuanLian(string cTable, int iID, string cZJBCode, int iDelFlag, SqlTransaction tran)
        {
            string sql = "";
            switch (cTable)
            {
                //销售订单，增加自由项
//                case "So_Somain":
//                    #region 销售订单，增加自由项,使用新编码用不到     
//                    sql = string.Format(@"  update a set cfree5 =case when b.bFree5=1 then '是' else null end,
//cfree6 =case when b.bFree6=1 then 'A' else null end,
//cfree7 =case when b.bFree7=1 then 'XM' else null end,
//cfree10 =case when b.bFree10=1 then '无' else null end from SO_SODetails a,inventory b where a.cInvCode= b.cInvCode
//AND A.ID= '{0}'", iID);
//                    DbHelper2.ExecuteSqlWithTrans(sql, tran);
//                    #endregion
//                    break;
                //采购订单，增加自由项
//                case "po_pomain":
//                    #region 销售订单，增加自由项     
//                    sql = string.Format(@"  update a set cfree5 =case when b.bFree5=1 then '是' else null end,
//cfree6 =case when b.bFree6=1 then 'A' else null end,
//cfree7 =case when b.bFree7=1 then 'XM' else null end,
//cfree10 =case when b.bFree10=1 then '无' else null end from po_podetails a,inventory b where a.cInvCode= b.cInvCode
//AND A.poID= '{0}'", iID);
//                    DbHelper2.ExecuteSqlWithTrans(sql, tran);
//                    #endregion
//                    break;
                case "dispatchlist":
                      
                    //更新发货单客户、币种,根据csocode关联
                    sql = string.Format(@" UPDATE a SET cexch_name=b.cexch_name,a.iExchRate=b.iExchRate,a.cCusCode=b.cCusCode,a.cinvoicecompany=b.cCusCode FROM dbo.DispatchList a,dbo.SO_SOMain b WHERE a.cSOCode=b.cSOCode
and a.dlid = {0}", iID);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);
                    //更新发货单上子表isosid及单价
                    sql = string.Format(@" UPDATE a SET isosid = c.iSOsID ,iUnitPrice =c.iUnitPrice,a.iTaxUnitPrice=c.iTaxUnitPrice,iQuotedPrice =c.iQuotedPrice,
iNatUnitPrice =c.iNatUnitPrice,a.iMoney=ROUND(c.iUnitPrice*a.iQuantity,2),isum=ROUND(c.iTaxUnitPrice*a.iQuantity,2),
a.iTax = ROUND(c.iTaxUnitPrice*a.iQuantity,2)-ROUND(c.iUnitPrice*a.iQuantity,2),
a.iNatSum = CASE WHEN b.cexch_name<>'人民币' then ROUND(c.iNatUnitPrice*a.iQuantity,2) ELSE ROUND(c.iTaxUnitPrice*a.iQuantity,2) end,
a.iNatMoney= CASE WHEN b.cexch_name<>'人民币' then ROUND(c.iNatUnitPrice*a.iQuantity,2) ELSE ROUND(c.iUnitPrice*a.iQuantity,2) end,
a.iNatTax = CASE WHEN b.cexch_name<>'人民币' THEN 0 ELSE ROUND(c.iTaxUnitPrice*a.iQuantity,2)-ROUND(c.iUnitPrice*a.iQuantity,2) END
 FROM dbo.DispatchLists a
INNER JOIN dbo.SO_SOMain b ON a.cordercode = b.csocode
INNER JOIN dbo.SO_SODetails c ON a.iorderrowno = c.iRowNo AND b.id = c.ID
 WHERE a.DLID = {0}   SELECT @@ROWCOUNT ", iID);
                    int iUptCnt = DbHelper.GetDbInt(DbHelper2.GetSingleWithTrans(sql, tran));
                    if (iUptCnt == 0)
                    {
                        return "无法关联销售订单";
                    }


                    sql = string.Format(@"  update a set bReturnFlag =1 from DispatchList a,DispatchLists b where a.dlid = b.dlid 
and b.iQuantity<0 and a.bReturnFlag=0  and a.dlid ={0}", iID);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);

                    //更新销售订单的发货数量，发货金额
                    sql = string.Format(@" UPDATE a SET iFHQuantity=isnull(iFHQuantity,0)+b.sl,a.iFHMoney=isnull(a.iFHMoney,0)+b.je FROM dbo.SO_SODetails a
INNER JOIN (SELECT iSOsID,SUM(iQuantity) sl,SUM(isum) je FROM dbo.DispatchLists WHERE dlid ={0} GROUP BY iSOsID) b
ON a.isosid = b.iSOsID", iID);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);

                    //更新业务员
                    sql = string.Format(@" update a set cdepcode=b.cdepcode, a.cPersonCode=b.cPersonCode 
from DispatchList a,so_somain b where a.csocode  = b.csocode and a.dlid ={0}", iID);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);



                    sql = string.Format(@"insert into SCM_entryledgerbuffer    
 (Subject,iNum,iQuantity,cWhCode,cInvCode,ItemId,    
  cVMIVenCode,cBatch,    
  DocumentType,DocumentId,DocumentDId,DemandPlanType,    
  DemandPlanDId,cFree1,cFree2,cFree3,cFree4,cFree5,    
  cFree6,cFree7,cFree8,cFree9,cFree10,dMDate,dVDate,    
  imassunit,imassday,cBusType,cSource,ddate,iBillNum,iBillQuantity,cExpirationdate,dExpirationdate,iExpiratDateCalcu)    
 select   '0301' 
  as Subject,      
b.inum*(-1) as inum,    
 b.iquantity*(-1)  as iQuantity, 
  b.cWhCode,b.cInvCode,0 as ItemId,    
  isnull(b.cVMIVenCode,'') as cVMIVenCode ,isnull(b.cBatch,'') as cBatch,    
  a.cvouchtype as DocumentType,a.dlid as DocumentId,b.autoid as DocumentDId,    
0 as DemandPlanType,    
  0 as DemandPlanDId,  
  isnull(b.cFree1,'') as cfree1,    
  isnull(b.cFree2,'') as cfree2,isnull(b.cFree3,'') as cfree3,isnull(b.cFree4,'') as cfree4,    
  isnull(b.cFree5,'') as cfree5,isnull(b.cFree6,'') as cfree6,isnull(b.cFree7,'') as cfree7,    
  isnull(b.cFree8,'') as cfree8,isnull(b.cFree9,'') as cfree9,isnull(b.cFree10,'') as cfree10,    
  b.dMDate as dMDate,b.dVDate,    
  b.cmassunit as imassunit,b.imassdate as imassday,a.cBusType,'销售订单',a.ddate,    
 0 AS iBillNum,    
  0 as iBillQuantity ,  
  b.cExpirationdate,b.dExpirationdate ,b.iExpiratDateCalcu  
  from dbo.DispatchList a with (nolock) inner join dbo.DispatchLists b with (nolock) on a.dlid=b.dlid     
  left join inventory i on b.cinvcode=i.cinvcode     
 where a.dlid ={0}   ", iID);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);

                    sql = string.Format(@"
DECLARE @transactionid VARCHAR(20)      
select @transactionid = 'spid_' + convert(nvarchar(10),@@spid)   
insert into SCM_Item(cInvCode,cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10)
 select distinct cInvCode,cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10  from SCM_EntryLedgerBuffer a with (nolock) 
 where a.transactionid=@transactionid and not exists (select 1 from SCM_Item Item where Item.cInvCode=a.cInvCode and Item.cfree1=a.cfree1 
 and Item.cfree2=a.cfree2 and Item.cfree3=a.cfree3 and Item.cfree4=a.cfree4 and Item.cfree5=a.cfree5 
 and Item.cfree6=a.cfree6 and Item.cfree7=a.cfree7 and Item.cfree8=a.cfree8 and Item.cfree9=a.cfree9 and Item.cfree10=a.cfree10  )  
                    exec dbo.Usp_SCM_CommitEntryLedger @transactionid  
                    exec IA_SP_WriteUnAccountVouchForSA  {0},N'05'", iID);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);
                    break;
                case "PU_ArrivalVouch":
                    #region 采购订单回写                   
                    
                    //更新采购入库数量
                    sql = string.Format(@" UPDATE a SET  iArrQTY=c.sl,fPoValidQuantity= CASE WHEN c.bGsp=1 THEN 0 ELSE c.sl end,fPoArrQuantity=c.sl FROM dbo.PO_Podetails a,
(SELECT  b.id,b.iPOsID,  bgsp,SUM(b.iquantity) sl  FROM dbo.PU_ArrivalVouchs b 
WHERE b.id = {0}   GROUP BY  b.id,b.iPOsID,  bgsp) c  WHERE a.id = c.iPOsID", iID);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);

                    //写辅助换算率
                    sql = string.Format(@" UPDATE a SET iinvexchrate=c.iChangRate,inum =a.iQuantity/iinvexchrate ,a.fRealNum=a.fRealQuantity/c.iChangRate,
a.fValidNum=fValidQuantity/iinvexchrate FROM dbo.PU_ArrivalVouchs a,dbo.Inventory b,dbo.ComputationUnit c WHERE a.cInvCode=b.cInvCode AND 
b.cgroupcode=c.cgroupcode AND a.cUnitID = c.cComunitCode
AND a.id = {0}", iID);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);

                    #endregion
                    break;
                case "QMINSPECTVOUCHER":
                    #region 报检单回写                   

                    //更新到货单检验数量
                    sql = string.Format(@"  UPDATE  A SET  a.bInspect=1,a.fInspectQuantity=b.FQUANTITY FROM PU_ArrivalVouchs a,dbo.QMINSPECTVOUCHERS b 
WHERE a.autoid = b.SOURCEAUTOID AND B.ID = {0}", iID );
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);
                   

                    #endregion
                    break;
                case "rdrecord01":
                    #region 入库单回写  ,审核状态                  
                    //更新iposid
                    sql = string.Format(@"  update a  set  iPOsID = c.ID from rdrecords01 a,PO_Pomain b,PO_Podetails c
 where a.cPOID = b.cPOID and a.cDefine22  =c.ivouchrowno and a.id = {0}", iID);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);


                    //更新采购入库数量
                    sql = string.Format(@"  UPDATE a SET ireceivedqty=ISNULL(ireceivedqty,0)+b.sl*(1) FROM  PO_Podetails a,(SELECT iposid,SUM(iquantity) sl FROM rdrecords01 WHERE id  = {0} GROUP BY iPOsID) b
WHERE a.id = b.iPOsID", iID, iDelFlag);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);
                    //=1 代表插入，更新库存
                    if (iDelFlag == 1)
                    {
                        sql = string.Format(@"
DECLARE @transactionid VARCHAR(20)      
select @transactionid = 'spid_' + convert(nvarchar(10),@@spid)  
EXEC ST_SaveForStock N'01',{0},0,0,1

insert into SCM_Item(cInvCode,cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10)
 select distinct cInvCode,cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10  from SCM_EntryLedgerBuffer a with (nolock) 
 where a.transactionid=@transactionid and not exists (select 1 from SCM_Item Item where 
 Item.cInvCode=a.cInvCode 
 and isnull(Item.cfree1,'')=isnull(a.cfree1,'') 
 and isnull(Item.cfree2,'')=isnull(a.cfree2,'')  and isnull(Item.cfree3,'')=isnull(a.cfree3,'') 
  and isnull(Item.cfree4,'')=isnull(a.cfree4,'') and isnull(Item.cfree5,'')=isnull(a.cfree5,'') 
   and isnull(Item.cfree6,'')=isnull(a.cfree6,'') and isnull(Item.cfree7,'')=isnull(a.cfree7,'') 
 and isnull(Item.cfree8,'')=isnull(a.cfree8,'')  and isnull(Item.cfree9,'')=isnull(a.cfree9,'')  
 and isnull(Item.cfree10,'')=isnull(a.cfree10,'')   )
 
exec dbo.Usp_SCM_CommitEntryLedger @transactionid
update rdrecord01 set  chandler = cmaker,dveridate =ddate,dnverifytime = getdate() where id={0}
exec IA_SP_WriteUnAccountVouchForST  {0},N'01'", iID);

                        DbHelper2.ExecuteSqlWithTrans(sql, tran);
                    }
                    else
                    {
                        sql = string.Format(@"

update rdrecord01 set  chandler = null,dveridate =null,dnverifytime = null where id={0}
DECLARE @transactionid VARCHAR(20)      
select @transactionid = 'spid_' + convert(nvarchar(10),@@spid)  
EXEC ST_SaveForStock N'01',{0},0,1,1
exec dbo.Usp_SCM_CommitEntryLedger @transactionid
 ", iID);

                        DbHelper2.ExecuteSqlWithTrans(sql, tran);
                        //存货核算异常单据整理
                    }
                    #endregion
                    break;
                case "rdrecord32":

                    //更新发货单累计出库数量
                    sql = string.Format(@" 
update so_sodetails set foutquantity =  isnull(a.fOutQuantity,0)+b.iqty*{1} from so_sodetails a,  dbo.DispatchLists c,(select  b.iDLsID,SUM(isnull(b.iQuantity,0)) iqty from   dbo.rdrecord32 a,rdrecords32 b WHERE a.id  =b.id AND a.id ={0} group by iDLsID) b where c.iDLsID = b.idlsid AND a.iSOsID = c.iSOsID
--更新发货单
update DispatchLists set fOutQuantity = ISNULL(fOutQuantity,0)+b.iqty*{1} 
FROM DispatchLists a,(select  b.iDLsID,SUM(isnull(b.iQuantity,0)) iqty from   dbo.rdrecord32 a,rdrecords32 b WHERE a.id  =b.id AND a.id ={0} group by iDLsID) b where a.iDLsID = b.idlsid
", iID, iDelFlag);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);
                    //发货单标记回写
                    if (iDelFlag == 1)
                    {
                        sql = string.Format(@"UPDATE DispatchList SET cSaleOut = 'ST' FROM DispatchList A,rdrecord32 B WHERE A.dlid = B.cdlcode
AND b.id ={0}", iID);
                    }
                    else
                    {
                        sql = string.Format(@"UPDATE DispatchList SET cSaleOut = null FROM DispatchList A,rdrecord32 B WHERE A.dlid = B.cdlcode
AND b.id ={0}", iID);
                    }
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);
                    //库存回写
                    if (iDelFlag == 1)
                    {
                        sql = string.Format(@"
DECLARE @transactionid VARCHAR(20)      
select @transactionid = 'spid_' + convert(nvarchar(10),@@spid)  
EXEC ST_SaveForStock N'32',{0},0,0,1

insert into SCM_Item(cInvCode,cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10)
 select distinct cInvCode,cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10  from SCM_EntryLedgerBuffer a with (nolock) 
 where a.transactionid=@transactionid and not exists (select 1 from SCM_Item Item where 
 Item.cInvCode=a.cInvCode 
 and isnull(Item.cfree1,'')=isnull(a.cfree1,'') 
 and isnull(Item.cfree2,'')=isnull(a.cfree2,'')  and isnull(Item.cfree3,'')=isnull(a.cfree3,'') 
  and isnull(Item.cfree4,'')=isnull(a.cfree4,'') and isnull(Item.cfree5,'')=isnull(a.cfree5,'') 
   and isnull(Item.cfree6,'')=isnull(a.cfree6,'') and isnull(Item.cfree7,'')=isnull(a.cfree7,'') 
 and isnull(Item.cfree8,'')=isnull(a.cfree8,'')  and isnull(Item.cfree9,'')=isnull(a.cfree9,'')  
 and isnull(Item.cfree10,'')=isnull(a.cfree10,'')   )
 
exec dbo.Usp_SCM_CommitEntryLedger @transactionid
update rdrecord32 set  chandler = cmaker,dveridate =ddate,dnverifytime = getdate() where id={0}
exec IA_SP_WriteUnAccountVouchForST  {0},N'32'", iID);

                        DbHelper2.ExecuteSqlWithTrans(sql, tran);
                    }
                    else
                    {
                        sql = string.Format(@"

update rdrecord32 set  chandler = null,dveridate =null,dnverifytime = null where id={0}
DECLARE @transactionid VARCHAR(20)      
select @transactionid = 'spid_' + convert(nvarchar(10),@@spid)  
EXEC ST_SaveForStock N'32',{0},0,1,1
exec dbo.Usp_SCM_CommitEntryLedger @transactionid
 ", iID);

                        DbHelper2.ExecuteSqlWithTrans(sql, tran);
                        //存货核算异常单据整理
                    }
                  
                    break;

                default:
                    break;


            }
            return "ok";
        }


        #endregion
    }
}
